SitePoint Sponsor

User Tag List

Results 1 to 5 of 5

Hybrid View

  1. #1
    SitePoint Zealot
    Join Date
    Jun 2004
    Location
    Belgium
    Posts
    133
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Question How to optimise this query?

    I'm trying to sort out some of my slowest queries, and here is one that takes some time, partially because it's a bit slow, but also because I run it quite often.

    Anyway, my table site_articles has seperate indexes on approved (int), type (int) and created (datetime), and the only one used is created. There is however a full tablescan occurring...

    SELECT id, title, created, headline FROM site_articles WHERE approved=1 AND type=0 AND id <> 14277 AND DATE(created) = DATE('20100225') ORDER BY created DESC LIMIT 15

    Is there any way I can improve this one? Note that I can pass the date '20100225' in any format, so I can change that if necessary.

    Thanks

  2. #2
    Utopia, Inc. silver trophy
    ScallioXTX's Avatar
    Join Date
    Aug 2008
    Location
    The Netherlands
    Posts
    8,892
    Mentioned
    138 Post(s)
    Tagged
    2 Thread(s)
    Putting a function in the WHERE clause will always result in a table scan AFAIK.
    What is the data type of created? What values does it contain?

    Also, could you post a SHOW CREATE TABLE for the table in question and the result of an EXPLAIN for the query ?
    Rémon - Hosting Advisor

    Minimal Bookmarks Tree
    My Google Chrome extension: browsing bookmarks made easy

  3. #3
    SitePoint Zealot
    Join Date
    Jun 2004
    Location
    Belgium
    Posts
    133
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Type of created is "datetime" as mentioned in my post.

    Explain result:
    Code:
    id 	select_type 	table 	type 	possible_keys 	key 	key_len 	ref 	rows 	Extra
    1 	SIMPLE 	site_articles 	index 	PRIMARY,type,approved 	created 	9 	NULL 	15 	Using where
    Table structure:
    Code MySQL:
    CREATE TABLE `site_articles` (
     `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
     `authorid` int(10) unsigned NOT NULL DEFAULT '2',
     `title` varchar(80) CHARACTER SET utf8 DEFAULT NULL,
     `created` datetime DEFAULT NULL,
     `type` tinyint(2) unsigned NOT NULL DEFAULT '0',
     `approved` tinyint(1) unsigned NOT NULL DEFAULT '1',
     PRIMARY KEY (`id`),
     KEY `created` (`created`),
     KEY `type` (`type`),
     KEY `approved` (`approved`),
     FULLTEXT KEY `title` (`title`)
    ) ENGINE=MyISAM AUTO_INCREMENT=15936 DEFAULT CHARSET=utf8 COLLATE=utf8_bin PACK_KEYS=1

  4. #4
    Utopia, Inc. silver trophy
    ScallioXTX's Avatar
    Join Date
    Aug 2008
    Location
    The Netherlands
    Posts
    8,892
    Mentioned
    138 Post(s)
    Tagged
    2 Thread(s)
    These kind of things is always a bit of trail and error. For this scenario I would first try an index on the PK, approved and type together.

    How many rows does the table contain (estimate)? Is it mostly selects or are there a lot of INSERT and UPDATE queries fired on it as well?
    Rémon - Hosting Advisor

    Minimal Bookmarks Tree
    My Google Chrome extension: browsing bookmarks made easy

  5. #5
    SitePoint Zealot
    Join Date
    Jun 2004
    Location
    Belgium
    Posts
    133
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Ah yes found a solution!
    Query execution down from 0.27s avg to 0.0016s !

    Code MySQL:
    SELECT id, title, created, headline
    FROM site_articles
    WHERE approved =1
    AND TYPE =0
    AND id <>14277
    AND created
    BETWEEN TIMESTAMP( '20100225000000' )
    AND TIMESTAMP( '20100225235959' )
    ORDER BY created DESC
    LIMIT 15

    Removed the requirement to perform DATE(...) on every row. Thanks for the hint on functions in WHERE, made me think


Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •