Mysql – EXPLAIN output suggests that the index is not being used

MySQL

I have set up my table with an index only on done_status(done_status = INT):

enter image description here

When I use:

EXPLAIN SELECT * FROM reminder  WHERE done_status=2

I get this back:

id  select_type  table     type  possible_keys  key  key_len  ref  rows  Extra
1   SIMPLE       reminder  ALL   done_status    NULL NULL     NULL 5     Using where

But when I issue this command:

EXPLAIN SELECT * FROM reminder  WHERE done_status=1

I get the following returned:

id  select_type  table     type  possible_keys  key          key_len  ref   rows  Extra
1   SIMPLE       reminder  ref   done_status    done_status  4        const 2   

The EXPLAIN shows me that it uses 5 rows, the second time 2 rows.

I don't think the index is used, if I understood it right first time it should give me 3 rows. What do I do wrong?

SHOW INDEX FROM reminder:

Table    Non_unique  Key_name     Seq_in_index  Column_name  Collation  Cardinality  Sub_part  Packed  Null  Index_type  Comment  Index_comment
reminder 1           done_status  1             done_status  A          5            NULL      NULL          BTREE

explain extended:

id  select_type  table     type  possible_keys  key          key_len  ref    rows  filtered  Extra
1   SIMPLE       reminder  ref   done_status    done_status  4        const  2     100.00

show warnings didn't show anything of interest.

Best Answer

You misunderstand what the 'rows' field is. It is the number of rows that mysql estimates it shall need to read to satisfy your query. This value can be quite inaccurate. It does not mean this is the number of rows in the result - or the actual number of rows read by mysql