Hi
I have this query:
SELECT id, name, YEAR(born) AS year
FROM people
WHERE DAYOFMONTH(born) = 7
AND MONTH(born) = 12
ORDER BY year DESC, name
operating on this table:
CREATE TABLE `people` (
`id` int(11) NOT NULL auto_increment,
`name` varchar(100) NOT NULL default '',
`born` date NOT NULL default '0000-00-00',
`died` date NOT NULL default '0000-00-00',
`imdb` int(11) NOT NULL default '0',
`allmusic` varchar(25) NOT NULL default '',
`allmovies` varchar(25) NOT NULL default '',
`uri` varchar(100) NOT NULL default '',
`image` blob NOT NULL,
PRIMARY KEY (`id`),
UNIQUE KEY `imdb` (`imdb`),
KEY `born` (`born`),
KEY `died` (`died`),
FULLTEXT KEY `name_2` (`name`)
) TYPE=MyISAM AUTO_INCREMENT=314541 ;
which has 169,549 rows.
EXPLAIN SELECT shows that it has type ALL, and no possible keys. It
shows 'using where, using filesort'. The query is quite slow.
How could I optimise this query?
Thanks in advance
Jasper Bryant-Greene