Slow MySQL (InnoDB) query despite join on indexed key, why?

Do you have any idea why this request could be slow (1.7 s on a otherwise fast MySQL server):

SELECT DISTINCT TABLE_A.keyA 
FROM TABLE_A,TABLE_B 
WHERE TABLE_A.keyB= TABLE_B.keyB 
AND TABLE_A.fieldC in (0,2,5,7,8) LIMIT 20;

With this execution plan given by EXPLAIN:

id    select_type table       type    possible_keys         key                   key_len   ref     rows     Extra 
1     SIMP开发者_运维问答LE      TABLE_B     index   PRIMARY               PRIMARY               8     NULL      10     Using index; Using temporary
1     SIMPLE      TABLE_A     ref     IDX_TABLE_A_KEY_B     IDX_TABLE_A_KEY_B     8     TABLE_B.keyB     25455     Using where

Other elements :

• the table TABLE_A has 300 000 lines
• TABLE_A.keyA is the primary key of TABLE_A
• TABLE_A.keyB is a foreign key towards the primary key keyB of TABLE_B ; the table TABLE_B has 10 lines ;
• 99 % of TABLE_A has fieldC=1 and 1 % of the table has fieldC in (0,2,5,7,8) (that is why this field is not indexed ; EDIT this clause is not problematic because a SELECT with the same clause but no join with TABLE_B is fast) ;
• it seems to me that it is the JOIN that is guilty, because a simple SELECT on the table is fast ;
• a join with another table TABLE_C is also very slow ;
• MySQL version : 5.1.23a-maria-alpha

Do you have any idea?

---

99 % of TABLE_A has fieldC=1 and 1 % of the table has fieldC in (0,2,5,7,8) (that is why this field is not indexed

That would be an excellent reason to index the column. Your query only wants less than 1% of the table, so an index would be very selective.

---

Probably the IN clause, they tend to be quite slow, have you got an index on TABLE_A.fieldC?

---

SELECT DISTINCT TABLE_A.keyA 
FROM TABLE_A 
INNER JOIN TABLE_B ON TABLE_A.keyB = TABLE_B.keyB 
WHERE 
TABLE_A.fieldC IN (0, 2, 5, 7, 8) LIMIT 20;

Try that one out.

---

try

SELECT DISTINCT TABLE_A.keyA 
FROM TABLE_A
WHERE TABLE_A.keyB IN (SELECT TABLE_B.keyB FROM TABLE_B )
AND TABLE_A.fieldC in (0,2,5,7,8) LIMIT 20;

---

Add index on column fieldC. Then run the query

Select DISTINCT keyA from 
 (select keyA, keyB from TABLE_A where fieldC in (0,2,5,7,8)) 
as temp STRAIGHT_JOIN TABLE_B on temp.keyB=TABLE_B.keyB limit 20

Try with straight join and inner join.