Mysql Index Being 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 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.

Answer 1

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.