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: 102
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