How to optimise this query?

Databases
1

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

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 ?

3

Type of created is “datetime” as mentioned in my post.

Explain result:

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:

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

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?

5

Ah yes found a solution!
Query execution down from 0.27s avg to 0.0016s !

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 :wink: