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.
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 ?
Type of created is "datetime" as mentioned in my post.
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
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
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?
Ah yes found a solution!
Query execution down from 0.27s avg to 0.0016s !
SELECT id, title, created, headline
WHERE approved =1
AND TYPE =0
AND id <>14277
BETWEEN TIMESTAMP( '20100225000000' )
AND TIMESTAMP( '20100225235959' )
ORDER BY created DESC
Removed the requirement to perform DATE(...) on every row. Thanks for the hint on functions in WHERE, made me think