I was suggested to post this here instead, orginal thread:
http://forums.mysql.com/read.php?10,93344,93344#msg-93344
I can't get mysql to use index when order by a datetime col.
Isn't possible? Explain Select returns "Using filesort" in the extra col.
----------------------------------
EXPLAIN SELECT * FROM testindextable ORDER BY thetime DESC
id select_type table type possible_keys key key_len ref rows Extra
1 SIMPLE testindextable ALL NULL NULL NULL NULL 4 Using filesort
----------------------------------
EXPLAIN testindextable
Field Type Null Key Default Extra
id int(10) unsigned NO PRI NULL auto_increment
name varchar(64) NO
thetime datetime NO MUL
----------------------------------
CREATE TABLE `testindextable` (
`id` int(10) unsigned NOT NULL auto_increment,
`name` varchar(64) NOT NULL,
`thetime` datetime NOT NULL,
PRIMARY KEY (`id`),
KEY `thetime` (`thetime`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1 AUTO_INCREMENT=1;