No index on! =?

Question

Consider the following two examples:

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 request != Have a NULL key? Why doesn't he use title ? What makes operator = use index but not != ?

Answer 1

It makes no sense to use an index, unless the title exactly 'The' very often.

Since almost every row should be selected, you get nothing from using the index. Actually, it can be expensive to use an index, which is probably determined by your MySQL engine, so it prefers not to use an index.

Compare the amount of work done in the following two situations:

Using the index:

1) Read the entire index tree in memory.
2) Find the index tree for "The" and filter these entries.
3) Read each row, with a few exceptions (which are probably in the same blocks on the disk as the rows you want to read, so really the whole table is likely to be read) from the table into memory.

Without index:

1) Read each line in memory and, while reading them, filter out any where title = 'The' from the result set