How to optimise this query

[Jasper Bryant-Greene]

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

[Bill Karwin]

Jasper Bryant-Greene wrote:

SELECT id, name, YEAR(born) AS year
FROM people
WHERE DAYOFMONTH(born ) = 7
AND MONTH(born) = 12
ORDER BY year DESC, name

I think this is a similar problem as trying to use an index for a query
like this:
SELECT ... FROM people WHERE allmusic LIKE '%foo';

The database cannot create an index for an arbitrary trailing substring.
Likewise, it cannot create an index on a calculation of a numeric or
date column. An index can be defined for simply a column name, not an
expression on that column (though the "expression index" is a feature of
some other RDBMS products, e.g. Firebird, so it is in fact possible).

To use an index for this query, you are going to have to store redundant
columns for DAYOFMONTH and MONTH. That is:

ALTER TABLE ADD COLUMN day_of_month_bo rn integer;
ALTER TABLE ADD COLUMN month_born integer;
UPDATE TABLE SET
day_of_month_bo rn = DAYOFMONTH(born ),
month_born = MONTH(born);
CREATE INDEX people_domb ON people(day_of_m onth_born);
CREATE INDEX people_mb ON people(month_bo rn);

Then do your query using the columns that have indexes on them:

SELECT id, name, YEAR(born) AS year
FROM people
WHERE day_of_month_bo rn = 7
AND month_born = 12
ORDER BY year DESC, name

Regards,
Bill K.