mysql where in 索引,MySQL索引-In与相等索引问题

Following queries run quite fast and instantaneously on mysql server:

SELECT table_name.id

FROM table_name

WHERE table_name.id in (10000)

SELECT table_name.id

from table_name

where table_name.id = (SELECT table_name.id

FROM table_name

WHERE table_name.id in (10000)

);

But if I change the second query to as following, then it takes more than 20 seconds:

SELECT table_name.id

from table_name

where table_name.id in (SELECT table_name.id

FROM table_name

WHERE table_name.id in (10000)

);

On doing explain, I get the following output. It is clear that there are some issues regarding how MySQL indexes the data, and use in keyword.

For first query:

+----+-------------+---------------+-------+---------------+---------+---------+-------+------+-------------+

| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |

+----+-------------+---------------+-------+---------------+---------+---------+-------+------+-------------+

| 1 | SIMPLE | table_name | const | PRIMARY | PRIMARY | 4 | const | 1 | Using index |

+----+-------------+---------------+-------+---------------+---------+---------+-------+------+-------------+

For second query:

+----+-------------+---------------+-------+---------------+---------+---------+-------+------+-------------+

| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |

+----+-------------+---------------+-------+---------------+---------+---------+-------+------+-------------+

| 1 | PRIMARY | table_name | const | PRIMARY | PRIMARY | 4 | const | 1 | Using index |

| 2 | SUBQUERY | table_name | const | PRIMARY | PRIMARY | 4 | | 1 | Using index |

+----+-------------+---------------+-------+---------------+---------+---------+-------+------+-------------+

For third query:

+----+--------------------+------------+-------+---------------+---------+---------+-------+---------+--------------------------+

| id | select_type | table_name | type | possible_keys | key | key_len | ref | rows | Extra |

+----+--------------------+------------+-------+---------------+---------+---------+-------+---------+--------------------------+

| 1 | PRIMARY | table_name | index | NULL | sentTo | 5 | NULL | 6250751 | Using where; Using index |

| 2 | DEPENDENT SUBQUERY | table_name | const | PRIMARY | PRIMARY | 4 | const | 1 | Using index |

+----+--------------------+------------+-------+---------------+---------+---------+-------+---------+--------------------------+

I am using InnoDB and have tried changing the third query to forcibly use the index as indicated by the following category.

解决方案

In first case you have only first record from subquery (It runs once, because equals is only for first value)

In second query you got Cartesian multiplication (each per each) because IN runs subquery for each row. Which is not good for performance

Try to use joins for these cases.