I have a table

CREATE TABLE activity ( 
 subscriberid INT(10) UNSIGNED NOT NULL DEFAULT '0', 
 action VARCHAR(250) NOT NULL DEFAULT '', 
 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; Jun 1 '14 at 12:28
  • @RolandoMySQLDBA the table has 4 rows if that's what you wanted to know :) result fo the query here take.ms/MMEMj Jun 2 '14 at 22:23

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

ALTER TABLE activity ADD INDEX subid_date(subscriberid, cdate);
  • without index. and for a select * i should cover all tables May 31 '14 at 23:13

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


Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.