Mysql index ignored

Question

EXPLAIN SELECT * FROM content_link link STRAIGHT_JOIN content ON link.content_id = content.id WHERE link.content_id = 1 LIMIT 10; +----+-------------+---------+-------+---------------+------------+---------+-------+------+-------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +----+-------------+---------+-------+---------------+------------+---------+-------+------+-------+ | 1 | SIMPLE | link | ref | content_id | content_id | 4 | const | 1 | | | 1 | SIMPLE | content | const | PRIMARY | PRIMARY | 4 | const | 1 | | +----+-------------+---------+-------+---------------+------------+---------+-------+------+-------+ 

However, when I delete WHERE, the request stops using the key (even if I explicitly force it)

 EXPLAIN SELECT * FROM content_link link FORCE KEY (content_id) STRAIGHT_JOIN content ON link.content_id = content.id LIMIT 10; +----+-------------+---------+--------+---------------+---------+---------+------------------------+---------+-------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +----+-------------+---------+--------+---------------+---------+---------+------------------------+---------+-------------+ | 1 | SIMPLE | link | index | content_id | PRIMARY | 7 | NULL | 4555299 | Using index | | 1 | SIMPLE | content | eq_ref | PRIMARY | PRIMARY | 4 | ft_dir.link.content_id | 1 | | +----+-------------+---------+--------+---------------+---------+---------+------------------------+---------+-------------+ 

Are there any workarounds?

I understand that in the second example, I select the entire table, but why did mysql suddenly decide that it would ignore my FORCE anyway and not use the key? Without a key, the request takes 10 minutes. Ugh.

Answer 1

FORCE is a bit wrong. Here is what MySQL says (my underline):

You can also use FORCE INDEX, which acts like a USE INDEX (index_list), but with the addition that scanning a table is considered very expensive. In other words, a table scan is used only if there is no way to use one of the specified indexes to search for rows in the table.

Since you do not actually find any rows (you select all of them), scanning the table will always be the fastest, and the optimizer is smart enough to know that contrary to what you tell them.

ETA:

Try adding ORDER BY to the primary key once, and I'm sure it will use the index.

Answer 2

An index helps you quickly search inside a table, but it just slows down if you select the entire table. Therefore, MySQL correctly ignores the index.

In your case, perhaps the index has a hidden side effect that is not known to MySQL. For example, if an inner join is performed for only a few rows, the index will speed things up. But MySQL cannot know this without an explicit hint.

There is an exception: when each selected column is inside the index, the index is still useful if you select each row. For example, if you have an index in LastName, the following query still uses the index:

 select LastName from orders 

But this will not be:

 select * from Orders 

Answer 3

Your content_id seems to be NULL .

Optimizer

MySQL believes that there is no guarantee that your query will return all values ​​using an index only (although there really is a guarantee since you are using a column in JOIN )

That is why it returns to a full table check.

Or add the NOT NULL condition:

 SELECT * FROM content_link link FORCE KEY (content_id) STRAIGHT_JOIN content ON content.id = link.content_id WHERE link.content_id IS NOT NULL LIMIT 10; 

or mark the column as NOT NULL :

 ALTER TABLE content_link MODIFY content_id NOT NULL 

Update:

This is confirmed by error 45314 in MySQL .