weird index behavior

Posted by TasostheGreat on Stack Overflow See other posts from Stack Overflow or by TasostheGreat
Published on 2011-11-26T01:27:58Z Indexed on 2011/11/26 1:50 UTC
Read the original article Hit count: 105

Filed under:

enter image description here

I have set up my table with an index only on done_status(done_status =INT), 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 give this command

EXPLAIN SELECT * FROM reminder  WHERE done_status=1

that's what I get back:

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

first time it shows me it uses 5 rows second time 2 rows

I don't think the index works, 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

© Stack Overflow or respective owner

Related posts about mysql