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: 138
        
mysql

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