MySQL查询优化让我发疯了!几乎相同,但却大不相同

我有以下两个问题 (*) ,仅在限制在 WHERE name1 对 name2 ):

SELECT A.third_id, COUNT(DISTINCT B.fourth_id) AS num
FROM first A
JOIN second B ON A.third_id = B.third_id
WHERE A.name1 LIKE 'term%'

SELECT A.third_id, COUNT(DISTINCT B.fourth_id) AS num
FROM first A
JOIN second B ON A.third_id = B.third_id
WHERE A.name2 LIKE 'term%'

两个 name third_id 列以及 fourth_id (它们都是其他表的外键,但与此无关)。

EXPLAIN ,第一个行为是这样的-这就是我想要的:

+----+-------------+-------+-------+---------------+----------+---------+---------------+------+-------------+
| id | select_type | table | type  | possible_keys | key      | key_len | ref           | rows | Extra       |
+----+-------------+-------+-------+---------------+----------+---------+---------------+------+-------------+
|  1 | SIMPLE      | A     | range | third_id,name | name     | 767     | NULL          | 3491 | Using where | 
|  1 | SIMPLE      | B     | ref   | third_id      | third_id | 4       | db.A.third_id |   16 |             | 
+----+-------------+-------+-------+---------------+----------+---------+---------------+------+-------------+

第二个做这个,我肯定做 不 想要:

+----+-------------+-------+------+----------------+----------+---------+---------------+--------+-------------+
| id | select_type | table | type | possible_keys  | key      | key_len | ref           | rows   | Extra       |
+----+-------------+-------+------+----------------+----------+---------+---------------+--------+-------------+
|  1 | SIMPLE      | B     | ALL  | third_id       | NULL     | NULL    | NULL          | 507539 |             | 
|  1 | SIMPLE      | A     | ref  | third_id,name2 | third_id | 4       | db.B.third_id |      1 | Using where | 
+----+-------------+-------+------+----------------+----------+---------+---------------+--------+-------------+

这里到底发生了什么事?我如何使第二个行为正常(如第一个)?

(*) 实际上,我没有。我有一些更复杂的查询;我已经删除了这篇文章的额外内容,并将它们提炼为仍然显示出问题行为的最小查询。另外,为了保护罪犯,名字也被改了。