mysql 忽略索引,Mysql索引被忽略了

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 remove the WHERE, the query stops using the key (even when i explicitly force it to)

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 work-arounds to this?

I realize I'm selecting the entire table in the second example, but why does mysql suddenly decide that it's going to ignore my FORCE anyway and not use the key? Without the key the query takes like 10 minutes.. ugh.

解决方案

FORCE is a bit of a misnomer. Here's what the MySQL docs say (emphasis mine):

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

Since you aren't actually "finding" any rows (you are selecting them all), a table scan is always going to be fastest, and the optimizer is smart enough to know that in spite of what you are telling them.

ETA:

Try adding an ORDER BY on the primary key once and I bet it'll use the index.