Shulou(Shulou.com)06/01 Report--

I do not know if you have any understanding of similar articles on the optimization of mysql through IN for INNER JOIN, today I am here to give you a brief talk. If you are interested, let's take a look at the text. I believe you will gain something after reading the optimization of mysql through IN for INNER JOIN.

SQL question:

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)

After reading this article on how to optimize mysql through IN for INNER JOIN, what do you think? If you want to know more about it, you can continue to follow our industry information section.

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.