Detailed explanation of mysql execution plan id is empty (UNION keyword)

Introduction

During the work process, slow queries are sometimes tuned. For MySQL SQL statement tuning, MySQL itself provides a powerful explain keyword for query analysis and execution plan. This article mainly introduces the relevant content about the MySQL execution plan ID being empty (UNION keyword), and shares it for your reference and study. Let's take a look at the detailed introduction.

The UNION operator is used to combine the result sets of two or more SELECT statements. For example, I have two tables. Table 1 records the data of male employees in the company, including age, name, and position. Table 2 records the data of female employees in the company, including fields such as name, home address, and mobile phone number. At this time, we want to query the collection of names in the two tables based on certain conditions. The UNION keyword will be used.

UNION can query multiple fields, but the SELECT statement must have the same number of columns. Columns must also have similar data types. Also, the order of the columns in each SELECT statement must be the same.

grammar

 SELECT column_name(s) FROM table_name1
 UNION
 SELECT column_name(s) FROM table_name2

The UNION operator will deduplicate the result set. If duplicate values ​​are allowed, you can use UNION ALL. There is no other difference between the two except the deduplication function.

Examples

The example here creates two tables based on the introduction and performs corresponding operations.

First, create two tables, user1 and user2. The table creation statements are as follows:

-- Create table statement CREATE TABLE user1 (
 id INT,
 age INT,
 NAME VARCHAR (5),
 position VARCHAR (8)
);
CREATE TABLE user2 (
 id INT,
 name VARCHAR(5),
 address VARCHAR(50),
 phone_number VARCHAR(20)
)

Secondly, insert the corresponding data, the sql is as follows:

-- Data insertion statement INSERT INTO user1
VALUES
 (
  1,
  20,
  'Zhang San',
  'Technical Director'
 );

INSERT INTO user1
VALUES
 (
  2,
  20,
  'Li Si',
  'Product Manager'
 );

INSERT INTO user1
VALUES
 (
  3,
  20,
  'Wang Wu',
  'Operations Director'
 );

INSERT INTO user1
VALUES
 (
  4,
  20,
  'Zhao Liu',
  'Logistics Manager'
 ) INSERT INTO user2 (
  id,
  NAME,
  address,
  phone_number
 )
VALUES
 (
  1,
  'Zhenzhen',
  'Beijing',
  '155332211'
 );

INSERT INTO user2 (
 id,
 NAME,
 address,
 phone_number
)
VALUES
 (
  2,
  'Lian Lian',
  'Shanghai',
  '155332211'
 );

INSERT INTO user2 (
 id,
 NAME,
 address,
 phone_number
)
VALUES
 (
  3,
  'Love love',
  'Shenzhen',
  '155332211'
 );

Finally, run the UNION statement to query

 -- UNION statement SELECT NAME FROM user1
 UNION
 SELECT name FROM user2

After the code is executed, the results are as follows:

The column names in the result set here are the column names of the first select statement of the UNION statement (user1 is NAME and user2 is name).

When explaining, id is null

When executing the UNION execution plan, the execution id of the last operation to merge the result set is null. Moreover, the select_type of the first execution is a simple query, the second and subsequent queries are UNION, and the select_type of the final merge is UNION RESULT.

The execution plan code is as follows:

EXPLAIN SELECT NAME FROM user1
UNION
SELECT name FROM user2

The results are as follows:

Summarize

The above is the full content of this article. I hope that the content of this article will have certain reference learning value for your study or work. If you have any questions, you can leave a message to communicate. Thank you for your support for 123WORDPRESS.COM.