Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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.

share|improve this question

migrated from stackoverflow.com Nov 26 '11 at 3:11

This question came from our site for professional and enthusiast programmers.

    
Trust me the index works. But I cant see anything easily in your screen shot - can you do a "show index from yourtable" –  Adrian Cornish Nov 26 '11 at 1:31
    
yeah edited my question –  TasostheGreat Nov 26 '11 at 1:36
    
please use glorify \G for the schema and explain plan result, it should be more readable –  ajreal Nov 26 '11 at 2:08
    
Out of interest can you repeat with a "explain extended" and a "show warnings" this will show the actual SQL mysql chooses –  Adrian Cornish Nov 26 '11 at 2:11
    
@ajreal what is glorify? –  TasostheGreat Nov 26 '11 at 2:14
show 2 more comments

2 Answers 2

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

share|improve this answer
    
So? Where did I say it does? What the optimizer chooses is up to it? The index still works. –  Adrian Cornish Nov 26 '11 at 1:55
    
@ajreal It does not mean the index is broken though. Just the optimizer choose (in its mind) the most efficient way to query the data. I assumed the OP was expecting the rows column in the EXPLAIN to be exact. It does not mean the index is broken - just that mysql choose not to use it (probably). –  Adrian Cornish Nov 26 '11 at 2:10
1  
@ajreal: I am missing something in your points. The rows column of an explain has nothing to do with indexes right? Mysql choose not to use the index (maybe all the data is in a single page). Not sure I am understanding your point? Query optimization on a 5 row table is going to produce some 'odd' results because it pretty much does not matter how you optimize. –  Adrian Cornish Nov 26 '11 at 2:15
    
let us continue this discussion in chat –  Adrian Cornish Nov 26 '11 at 2:21
    
In this case who cares what index the optimizer chooses? There is nothing wrong with the index itself, because the optimizer felt like it did not need it - what does it matter? –  Adrian Cornish Nov 26 '11 at 2:37
add comment

The first execution plain is not make use of index for sure,
it could be the information_schema.statistics on the index does not catch up with the data after some write operations, or the table is not been accessed for a long time.

as explain here :- http://dba.stackexchange.com/questions/3398/from-where-does-the-mysql-query-optimizer-read-index-statistics

for the second execution plan, it seems information_schema.statistics already catch up and fix the NULL cardinality issue.

Therefore, is execute the query according to the index optimizer.

For table with small rows, it does not matter much.
But data will growth, developer should always do a check on this,
and perform the necessary analyze table when encounter cardinality is null on the index.

share|improve this answer
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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