How to change IN into INNER JOIN in mysql Optimization

This article is to share with you about how to change IN for INNER JOIN in mysql optimization, the editor thinks it is very practical, so I share it with you to learn. I hope you can get something after reading this article.

When I was playing with the code today, I encountered a SQL problem:

To query the ID of table A, match the ID of table B, and query the entire contents of table B:

Before optimization:

MySQL [xxuer] > SELECT-> COUNT (*)-> FROM-> t_cmdb_app_version-> WHERE-> id IN (SELECT-> pid-> FROM-> t_cmdb_app_relation UNION SELECT-> rp_id-> FROM-> t_cmdb_app_relation) +-+ | COUNT (*) | +-+ | 266 | +-+ 1 row in set (0.21 sec)

After optimization:

MySQL [xxuer] > SELECT-> count (*)-> FROM-> t_cmdb_app_version a-> INNER JOIN-> (SELECT-> pid-> FROM-> t_cmdb_app_relation UNION SELECT-> rp_id-> FROM-> t_cmdb_app_relation) b ON a.id = b.pid +-+ | count (*) | +-+ | 266 | +-+ 1 row in set (0.00 sec)

View the execution plan comparison:

MySQL [xxuer] > explain SELECT-> COUNT (*)-> FROM-> t_cmdb_app_version-> WHERE-> id IN (SELECT-> pid-> FROM-> t_cmdb_app_relation UNION SELECT-> rp_id-> FROM-> t_cmdb_app_relation) +- -+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | + -+-+ | 1 | PRIMARY | t_cmdb_app_version | index | NULL | PRIMARY | 4 | NULL | 659 | Using where Using index | | 2 | DEPENDENT SUBQUERY | t_cmdb_app_relation | ALL | NULL | 383 | Using where | 3 | DEPENDENT UNION | t_cmdb_app_relation | ALL | NULL | 383 | Using where | NULL | UNION RESULT | ALL | NULL | Using | Temporary | +-+- -+ 4 rows in set (0.00 sec) MySQL [xxuer] > explain SELECT-> count (*)-> FROM-> t_cmdb_app_version a-> INNER JOIN-> (SELECT-> pid-> FROM-> t_cmdb_app_relation UNION SELECT-> Rp_id-> FROM-> t_cmdb_app_relation) b ON a.id = b.pid +- -+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +- -- +-+ | 1 | PRIMARY | | ALL | NULL | 766 | Using where | | 1 | PRIMARY | a | | eq_ref | PRIMARY | PRIMARY | 4 | b.pid | 1 | Using where | Using index | | 2 | DERIVED | t_cmdb_app_relation | ALL | NULL | 383 | NULL | 3 | UNION | t_cmdb_app_relation | ALL | NULL | 383 | NULL | NULL | UNION RESULT | ALL | NULL | NULL | NULL | NULL | NULL | Using temporary | +-- +- -+ 5 rows in set (0.00 sec) above is how to exchange IN for INNER JOIN in mysql optimization. The editor believes that there are some knowledge points that we may see or use in our daily work. I hope you can learn more from this article. For more details, please follow the industry information channel.