No index on !=?

Question

Consider the following two EXPLAINs:

EXPLAIN SELECT * FROM sales WHERE title != 'The'

id  select_type table   type    possible_keys   key key_len ref rows    Extra
1   SIMPLE      sales   ALL      title        NULL  NULL    NULL    41707   Using where

And -

EXPLAIN SELECT * FROM sales WHERE title = 'The'
id  select_type table   type    possible_keys   key key_len ref rows    Extra
1   SIMPLE      sales   ref      title         title    767 const   1   Using where 

Why does the != query have a NULL key? Why doesn't it use title? What causes a = statement to be able to utilize an index but not a !=?

Epizoon

Answer 1

There is no point on using the index unless title is exactly 'The' very frequently.

Since almost every row needs to be selected you don't gain anything from using an index. It can actually be costly to use an index, which is probably what your MySQL engine is determining, so it is opting not to use the index.

Compare the amount of work done in these two situations:

Using the index:

1) Read the entire index tree into memory.
2) Search the index tree for the value 'The' and filter out those entries.
3) Read every row except for the few exceptions (which probably are in the same blocks on the disk as rows that do need to be read, so really the whole table is likely to be read in) from the table into memory.

Without the index:

1) Read every row into memory and while reading them filter out any where title = 'The' from the result set

Hydantoin

To expand on that -- selecting every row where the title isn't "The" will return almost every row in the table anyway, so there's no point in using an index for this query at all. (Since you'll have to scan the whole table to return all those rows.) – Bucci