Why does MySQL choose the wrong index in a JOIN query?

Question

I use a simple query JOIN, however MySQL does not show the wrong index in the plan EXPLAIN.

It selects an index for a column that is not involved in the query.

The request is based on the primary key. I tried to remove the index, but then the optimizer selects another irrelevant index.

In my case, table a contains ~ 2.5 million records and table b ~ ~ 5 million records. Each entry in has ~ 2 entries in b.

I am using MySql 5.6.

I did ANALYZE, and CHECKin the tables.

The query takes about 70 seconds, it uses the wrong index and executes a nested loop, why?

SELECT 
IFNULL(SUM(a.val),0) as total
FROM a , b
where a.id = b.a_id;

1   SIMPLE  a   index   PRIMARY idx_a_c_id  5       2406691 Using index
1   SIMPLE  tv  ref idx_a_id idx_a_id   4   capb_1.a.id 1

# id, select_type, table, type, possible_keys, key, key_len, ref, rows, Extra
'1', 'SIMPLE', 'spd_transaction', 'index', 'PRIMARY', 'idx_a_c_id', '5', NULL, '2406691', 'Using index'
'1', 'SIMPLE', 'tv', 'ref', 'idx_a_id', 'idx_a_id', '4', 'a.id', '1', NULL

Answer 1

1) SQL ( ), ( ) () FTS (Full Table Scan).

FTS , .

SELECT 
IFNULL(SUM(a.val),0) as total
FROM a , b USE INDEX /* Specify no index here to ignore index and force FTS*/
where a.id = b.a_id;

, , ur.

2) JOIN "B", SQL JOIN .

SELECT 
IFNULL(SUM(a.val),0) as total
FROM a USE INDEX /* Specify no index here to ignore index and force FTS*/
;