1

I've a double-column index which isn't used but would be a perfect match for the query:

mysql> EXPLAIN SELECT COUNT(*) as cnt
    -> FROM foobar USE INDEX(index_status_created_at)
    -> WHERE foobar.created_at < "2017-06-20"
    -> AND foobar.status IN ("status1", "status2", "status3");
+----+-------------+---------------+------+--------------------------+------+---------+------+---------+-------------+
| id | select_type | table         | type | possible_keys            | key  | key_len | ref  | rows    | Extra       |
+----+-------------+---------------+------+--------------------------+------+---------+------+---------+-------------+
|  1 | SIMPLE      | foobar        | ALL  | index_status_created_at | NULL | NULL    | NULL | 4374176 | Using where |
+----+-------------+---------------+------+--------------------------+------+---------+------+---------+-------------+
1 row in set (0.00 sec)

Unfortunately, the status are not evenly distributed, so especially for these 3 status there ar enot many rows and the total result would be under 10% of the total table size.

Do you have any ideas why this index is not used at all?

2
  • 2
    Try this: SELECT (SELECT COUNT(*) FROM foobar WHERE created_at < '2017-06-20' AND status = 'status1') + (SELECT ... AND status = 'status2') + (SELECT ... AND status = 'status3') AS cnt ; - with all 3 selects identical except for the status check. Commented Jul 10, 2017 at 13:09
  • 2
    It would also help the question if you added the SHOW CREATE TABLE foobar; output. Commented Jul 10, 2017 at 13:10

1 Answer 1

1

The name of your index suggests, that the index is on (status, created), correct? Then it's exactly what you mentioned.

An index works best, when it's as selective as possible, or in better words:

The closer the result of number of distinct values / total number of rows is to 1, the better is the index.

When you have very few distinct values, it's often cheaper to just scan the entire table, than to look at the index and then find the corresponding entry in the table. That's most probably the reason, the index isn't used. The fact, that you also filter by date, doesn't help much, because you have created as second column in the index.

The order of the columns in a combined index is very important. Always put the more selective columns first. In your case it might help (very much), if your index would be (created, status) instead (depending on how much rows per day you have and how much rows in total).

1
  • But that should have been considered a "covering" index - and do a full index scan instead of a full table index. We gotta see SHOW CREATE TABLE; we are missing something.
    – Rick James
    Commented Jul 10, 2017 at 22:01

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

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