I have a table
CREATE TABLE activity ( id INT(10) UNSIGNED AUTO_INCREMENT, subscriberid INT(10) UNSIGNED NOT NULL DEFAULT '0', action VARCHAR(250) NOT NULL DEFAULT '', ip INT(10) UNSIGNED NOT NULL DEFAULT '0', cdate DATETIME NULL DEFAULT NULL, PRIMARY KEY (id), KEY cdate (cdate), KEY subscriberid (subscriberid) ) ENGINE = InnoDB DEFAULT CHARSET = utf8 DEFAULT COLLATE = utf8_general_ci;
how can i return all rows for a single subscriberid ordered by cdate descending, using the index on cdate field.
if i do
explain select * from activity where subscriberid = 2 ORDER BY cdate DESC;
it seems not to use the index. If i add a group by clause it doesn't do a filesort as you can see in the link but it still scans all rows http://take.ms/8z3Po