MySQL not using index when checking = 1 , but using it with = 0

Question

Here is a perplexing issue I am having:

Query:
EXPLAIN SELECT id,hostname FROM queue_servers WHERE live=1

id  select_type     table   type    possible_keys   key     key_len     ref     rows    Extra

1   SIMPLE  queue_servers   ALL     live    NULL    NULL    NULL    6   Using where

Query:
EXPLAIN SELECT id,hostname FROM queue_servers WHERE live=0

id  select_type     table   type    possible_keys   key     key_len     ref     rows    Extra

1   SIMPLE  queue_servers   ref     live    live    1   const   1

SHOW INDEXES FROM queue_servers

Table Non_unique Key_name Seq_in_index Column_name Collation Cardinality Sub_part Packed Null Index_type

queue_servers   1   live    1   live    A   6   NULL    NULL        BTREE

Any ideas? This is making me go crazy.. If I just try selecting a single column like this:

EXPLAIN SELECT id FROM queue_servers WHERE live=1

It works just fine.. But if I try to select the column "hostname" , or add it to the select column list, it won't use the live index unless I am searching for live=0 .. Why is this?

The optimizer is free to use an index or not if it determines it would benefit the performance of the query. Do you have performance problems? — Lieven Keersmaekers
@Lieven - Agreed, even I had similar cases during sometime ago, and it does impact performance. Why would the optimizer chose an index based on the value of a column? I have seen it for evenly distributed values too. And sometimes it did that for the same value, same query, but run at a different time. Weird. Any explanations will be appreciated! — Jai
If your condition is going to select a large part of the table, using an index is actually slower. — bobflux
There's no point in using the index on your live column, it's cardinality is extremely low, MySQL will actually retrieve results faster if it doesn't use that index since scanning the index won't yield any performance at all. You should aim to index columns that have high cardinality (number of unique values of the column divided by number of total rows). As you can see, having 1 or 0 indexed doesn't really do anything, it just bloats your index and wastes space. — N.B.
Don't draw conclusions like these from a 6-row table. Make the table bigger. — Rick James

Johan Johan · Accepted Answer · 2011-05-22T00:08:38

Why doesn't MySQL not use an index?
MySQL will not use an index if a large percentage of the rows have that value.

Why will adding use index to the query not work here
Adding a use index clause will have no effect, because use index will only suggest which index to use, it will not suggest whether to use an index or not.

Caveat when using test tables with few rows
This is especially vexing when using test tables with few rows as MySQL will refuse to use an index, and it's hard to see what's wrong with your query.
So make sure you add enough rows to a test table to make it a realistic test.

Is using an index on low cardinality columns useless?
Indexing on boolean columns is not as useful as you thought before asking this question.
However it is also not useless either.
With InnoDB MySQL will try and retrieve data using the indexes if possible, if your boolean field has an index the query:

SELECT id, bool_field FROM table_with_many_columns_and_rows WHERE bool_field = 1

Can read all the data in the covering index for bool_field because secondary indexes in InnoDB always include the primary index (id) as well.
This is faster because MySQL does not have to read the entire table into memory.

In MyISAM this doesn't work and MySQL will examine the whole table.

But I can use force index
You can, but on low cardinality indexes it will make your query slower, not faster. Only override the indexes on complex queries and only if you know the rules MySQL uses to select indexes.

Links:
See: http://dev.mysql.com/doc/refman/5.1/en/mysql-indexes.html
and: http://www.xaprb.com/blog/2006/07/04/how-to-exploit-mysql-index-optimizations/

If you want a book on this subject: read
High performance MySQL: http://oreilly.com/catalog/9780596003067

MySQL not using index when checking = 1 , but using it with = 0

2 Answers