how to use an index on a mysql query with a where clause and a order by

Question

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

enter image description here

Please run this query and post the output: SELECT COUNT(1),subscriberid FROM activity GROUP BY subscriberid WITH ROLLUP;. If the output of that query is too long, then run these two queries and post their output: SELECT COUNT(1) FROM activity; and SELECT COUNT(1) FROM activity WHERE subscriberid = 2; — RolandoMySQLDBA, Jun 01 '14 at 12:28
@RolandoMySQLDBA the table has 4 rows if that's what you wanted to know :) result fo the query here http://take.ms/MMEMj — Nicola Peluchetti, Jun 02 '14 at 22:23

score 1 · Answer 1 · answered May 31 '14 at 21:17

1

Try a compound index on (subscriberid, cdate). i.e.

ALTER TABLE activity ADD INDEX subid_date(subscriberid, cdate);

answered May 31 '14 at 21:17

Jehad Keriaki

3,081
1
15
15

without index. and for a select * i should cover all tables – Nicola Peluchetti May 31 '14 at 23:13

score 0 · Answer 2 · edited Jun 15 '20 at 09:05

The MySQL Query Optimizer will never use any of your indexes. Why ?

Your Key distribution and the Cardinality of the indexes are way too low.

According to the output of the query I gave you and your own EXPLAIN plan, you only have 4 rows

3 rows with subscriber id 2
1 row with subscriber id 4

The MySQL Query Optimizer finds it very tedious to use an index on such a small dataset.

If you had 100 rows in the table with other subscriber id values, then the Query Optimizer will look closer and see which subscriber id makes up 5% of the rows or less. Anything above 5% make the Query Optimizer give up one index and try another. If none of the indexes can present a good key distribution and high cardinality, it will resort to a full table scan and some potential file sorting.

With only 4 rows in the table, the MySQL Query Optimizer made the right choice because it requires the least amount of work since it does not like key distribution and cardinality of the keys presented. Even compound indexes can suffer the same consequences.

I have other posts that discuss and demonstrate this Query Optimizer behavior

Apr 11, 2014 : Why is MySQL not using the index with the higher cardinality?
Nov 13, 2012 : Must an index cover all selected columns for it to be used for ORDER BY? (This post contains 7 other posts from me that stretch all the way back to July 2011 on this same subject)
Mar 05, 2012 : Tombstone Table vs Deleted Flag in database syncronization & soft-delete scenarios
Oct 20, 2011 : In MySQL, should I add an index even if the query that scans the table is only ran once a month?

how to use an index on a mysql query with a where clause and a order by

2 Answers2

Q.E.D.