I'd like to know if I can improve what I've done so that it works, or if MySQL just doesn't operate on date indexes in the way I understood.
I have a table of thousands of records which I need to filter based on a date range (i.e. pull every record between two dates). I've created the table, and I have an index on the date field. However, when I run the query below against the table, it does not seem to be using the index.
Below is the Table structure and the query. Attached is the result of the Explain.
CREATE TABLE `games_copy` (
`table_id` int(11) NOT NULL AUTO_INCREMENT,
`start_date` datetime DEFAULT NULL,
`end_date` datetime DEFAULT NULL,
PRIMARY KEY (`table_id`),
KEY `start_date_2` (`start_date`,`end_date`),
KEY `start_date` (`start_date`)
) ENGINE=MyISAM AUTO_INCREMENT=7221 DEFAULT CHARSET=latin1;
SELECT * FROM games_copy WHERE start_date >= '2011-09-01' AND start_date <= '2012-08-31'
explain SELECT * FROM games_copy WHERE start_date >= '2011-09-01' AND start_date <= '2012-08-31'