MySQL Optimization example: in swap INNER JOIN

I'm having a SQL problem with the code today:

To query the ID of a table, match the ID of Table B and query all the 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)

To view execution plan comparisons:

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    ->          &nBsp;   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          | NULL     | NULL    | NULL |  383 | Using where               | |   3 | DEPENDENT UNION    | t_cmdb_app_relation |  all   | null          | null     | NULL    | NULL |  383 | Using  where              | |  null | union result       | <union2,3>           | ALL   | NULL           | NULL    | NULL     | NULL | 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      |  <derived2>          | ALL     | null          | null    |  null    | 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          | null     | null    | null  |  383 |  null                     | |   3 | union        | t_cmdb_app_relation  | ALL    | NULL           | NULL    | NULL    | NULL  |   383 | NULL                      | |  NULL | UNION RESULT | <union2,3>           | ALL    | NULL           | null    | null    | null   | NULL | Using temporary           |+----+--------------+---------------------+--------+---------------+---------+---------+-------+------+--------------------------+5  rows in set  (0.00&NBSP;SEC)

This article is from the "next door Lao Zhang" blog, please be sure to keep this source http://xxuer.blog.51cto.com/11947593/1940438

MySQL Optimization example: in swap INNER JOIN