Analysis of MySQ constant transfer

MySQL supports constant transfer for expression simplification. For example: CREATETABLE 't1' ('id1' int (11) NOTNULLDEFAULT

MySQL supports constant transfer for expression simplification. For example, create table 't1' ('id1' int (11) NOT NULL DEFAULT

MySQL supports constant transfer for expression simplification, as shown in the following example:
Create table 't1 '(
'Id1' int (11) not null default '0 ',
'A1' int (11) default null,
'B1 'int (11) default null,
Primary key ('id1 ')
) ENGINE = InnoDB default charset = latin1;

Create table 't5 '(
'Id5' int (11) default null,
'A5 'int (11) default null,
'B5 'int (11) default null,
Unique key 'id5' ('id5 ')
) ENGINE = InnoDB default charset = latin1;

Insert data into each table.

The condition "a1 = a5 and a5 = 1" is optimized by the MySQL optimizer and changed to "('xx '. 't5 '. 'a5 '= 1) and ('xx '. 't1 '. 'a1' = 1) ", which is the constant transfer technology.

Mysql> explain extended select * from t5, t1 where a1 = a5 and a5 = 1;
+ ---- + ------------- + ------- + ------ + --------------- + ------ + --------- + ------ + ---------- + -------------------------------------------------- +
| Id | select_type | table | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+ ---- + ------------- + ------- + ------ + --------------- + ------ + --------- + ------ + ---------- + -------------------------------------------------- +
| 1 | SIMPLE | t5 | ALL | NULL | 10 | 100.00 | Using where |
| 1 | SIMPLE | t1 | ALL | NULL | 9999 | 100.00 | Using where; Using join buffer (Block Nested Loop) |
+ ---- + ------------- + ------- + ------ + --------------- + ------ + --------- + ------ + ---------- + -------------------------------------------------- +
2 rows in set, 1 warning (0.00 sec)

Mysql> show warnings;
/* Select #1 */select 'xx'. 't5 '. 'id5' AS 'id5', 'xx'. 't5'. 'a5 'AS 'a5 ',
'Xx '. 't5 '. 'b5 'AS 'b5', 'xx '. 't1 '. 'id1' AS 'id1', 'xx '. 't1 '. 'a1' AS 'a1', 'xx '. 't1 '. 'b1 'AS 'b1'
From 'xx'. 't5 'join 'xx'. 't1'
Where ('xx'. 't5 '. 'a5' = 1) and ('xx'. 'T1 '. 'a1' = 1 ))

2. When the conditional expression contains a primary key, the situation may be somewhat different, for example:
Mysql> explain extended select * from t5, t1 where id1 = id5 and id5 = 1;
+ ---- + ------------- + ------- + --------------- + --------- + ------- + ------ + ---------- + ------- +
| Id | select_type | table | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+ ---- + ------------- + ------- + --------------- + --------- + ------- + ------ + ---------- + ------- +
| 1 | SIMPLE | t5 | const | id5 | id5 | 5 | const | 1 | 100.00 | NULL |
| 1 | SIMPLE | t1 | const | PRIMARY | 4 | const | 1 | 100.00 | NULL |
+ ---- + ------------- + ------- + --------------- + --------- + ------- + ------ + ---------- + ------- +
2 rows in set, 1 warning (0.00 sec)

Mysql> show warnings;
/* Select #1 */select '1' AS 'id5', '1' AS 'a5 ', null as 'b5 ',
'1' AS 'id1', '1' AS 'a1', null as 'b1'
From 'xx'. 't5 'join 'xx'. 't1'
Where 1

Why?
This is because:
1 constant transfer technology makes MySQL optimizer think "id1 = id5 = 1"
2 while id1 and id5 are both primary keys, so t1 and t5 tables are considered as "constant tables". Therefore, the value of type in the execution plan is const. that is to say, t1-t5-table tuples can be completely determined (that is, they can be directly read during the optimization stage, rather than located at the execution stage ). therefore, the connection operation between table t1 and table t5 is simple.
3. When the execution plan is displayed, it is difficult to understand why it becomes "where 1 ". the condition does not work because the tuples have been found. the result shows that the condition exactly meets the output of the tuples of the constant table.

Therefore, it is easy to understand the constant transfer between the following primary key and a non-primary key.
Mysql> explain extended select * from t5, t1 where id1 = a5 and a5 = 1;
+ ---- + ------------- + ------- + --------------- + --------- + ------- + ------ + ---------- + ------------- +
| Id | select_type | table | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+ ---- + ------------- + ------- + --------------- + --------- + ------- + ------ + ---------- + ------------- +
| 1 | SIMPLE | t1 | const | PRIMARY | 4 | const | 1 | 100.00 | NULL |
| 1 | SIMPLE | t5 | ALL | NULL | 10 | 100.00 | Using where |
+ ---- + ------------- + ------- + --------------- + --------- + ------- + ------ + ---------- + ------------- +
2 rows in set, 1 warning (0.00 sec)

Mysql> show warnings;
/* Select #1 */select 'xx'. 't5 '. 'id5' AS 'id5', 'xx'. 't5'. 'a5 'AS 'a5 ',
'Xx'. 't5 '. 'b5' AS 'b5 ', '1' AS 'id1', '1' AS 'a1', null as 'b1'
From 'xx'. 't5 'join 'xx'. 't1'
Where ('xx'. 't5 '. 'a5' = 1)

-------------------------------------- Split line --------------------------------------

Install MySQL in Ubuntu 14.04

MySQL authoritative guide (original book version 2nd) Clear Chinese scan PDF

Ubuntu 14.04 LTS install LNMP Nginx \ PHP5 (PHP-FPM) \ MySQL

Build a MySQL Master/Slave server in Ubuntu 14.04

Build a highly available distributed MySQL cluster using Ubuntu 12.04 LTS

Install MySQL5.6 and Python-MySQLdb in the source code of Ubuntu 12.04

MySQL-5.5.38 universal binary Installation

-------------------------------------- Split line --------------------------------------

,