The process of quickly converting mysql left join to inner join

During the daily optimization process, I found a strange thing: the same SQL had two completely different execution plans, and even the driving table of left join could become different.

For left join, if the where condition contains a filter by the associated table, the left join may be converted to an inner join. In this case, shopInfo has the filter condition ShopCategory = 'LOC'; it is guaranteed that the record of shopInfo is not NULL, so the left join can be converted to an inner join during the optimization process. Then the JOIN order of O and S is interchangeable.

Verification conclusion:

Create the table:

--Class table CREATE TABLE T_CLASS(
  class_id int not null,
  class_name VARCHAR2(100)
);
Add index alter table T_CLASS add index inx_class_id(class_id);

--Student table CREATE TABLE T_STUDENT(
  student_id int not null,
  class_id int not null,
  student_name VARCHAR(100),
  age int,
  sex int 
)
Add index alter table T_STUDENT add index index_age(AGE);

--Insert class data into T_CLASS (CLASS_ID, CLASS_NAME)
values (1, 'Class 1');

insert into T_CLASS (CLASS_ID, CLASS_NAME)
values (2, 'Class 2');

insert into T_CLASS (CLASS_ID, CLASS_NAME)
values (3, 'three shifts');

insert into T_CLASS (CLASS_ID, CLASS_NAME)
values (4, 'fourth shift');

insert into T_CLASS (CLASS_ID, CLASS_NAME)
values (5, 'Fifth Class');

--Insert student data into T_STUDENT (STUDENT_ID, CLASS_ID, STUDENT_NAME, AGE, SEX)
values (1, 1, '李1', 3, '1');

insert into T_STUDENT (STUDENT_ID, CLASS_ID, STUDENT_NAME, AGE, SEX)
values (2, 1, '李2', 2, '1');

insert into T_STUDENT (STUDENT_ID, CLASS_ID, STUDENT_NAME, AGE, SEX)
values (3, 1, '李3', 3, '1');

insert into T_STUDENT (STUDENT_ID, CLASS_ID, STUDENT_NAME, AGE, SEX)
values (4, 2, '李4', 4, '1');

insert into T_STUDENT (STUDENT_ID, CLASS_ID, STUDENT_NAME, AGE, SEX)
values (5, 2, '李5', 3, '2');

insert into T_STUDENT (STUDENT_ID, CLASS_ID, STUDENT_NAME, AGE, SEX)
values (6, 2, '李6', 3, '1');

insert into T_STUDENT (STUDENT_ID, CLASS_ID, STUDENT_NAME, AGE, SEX)
values (7, 3, '李7', 6, '2');

insert into T_STUDENT (STUDENT_ID, CLASS_ID, STUDENT_NAME, AGE, SEX)
values (8, 3, '李8', 4, '2');

insert into T_STUDENT (STUDENT_ID, CLASS_ID, STUDENT_NAME, AGE, SEX)
values (9, 2, '李9', 2, '2');

insert into T_STUDENT (STUDENT_ID, CLASS_ID, STUDENT_NAME, AGE, SEX)
values (10, 2, '李10', 3, '1');

insert into T_STUDENT (STUDENT_ID, CLASS_ID, STUDENT_NAME, AGE, SEX)
values (11, 3, '李11', 3, '2');

insert into T_STUDENT (STUDENT_ID, CLASS_ID, STUDENT_NAME, AGE, SEX)
values (12, 2, '李12', 8, '2');

insert into T_STUDENT (STUDENT_ID, CLASS_ID, STUDENT_NAME, AGE, SEX)
values (13, 1, '李13', 6, '2');

Case 1: Table B has a where condition and is not null

Case 2: Both Table A and Table B have where conditions and are not null

Case 3: Table A and Table B both have where conditions and are not null, delete the index of Table B

in conclusion:

MySQL optimizer converts left join to inner join only when the associated table has a where condition and its filter condition is better than the associated table.

This is the end of this article about the process of quickly converting mysql left join to inner join. For more relevant mysql left join and inner join content, please search 123WORDPRESS.COM's previous articles or continue to browse the following related articles. I hope everyone will support 123WORDPRESS.COM in the future!

You may also be interested in:

A brief discussion on the underlying principle of mysql join
Analysis of usage scenarios of JOIN in SQL statements
MYSQL database basics - Join operation principle
How to solve the problem of invalid left join in MySQL and the precautions for its use
Why do code standards require SQL statements not to have too many joins?
MySQL efficient query left join and group by (plus index)
MySQL join buffer principle
Detailed explanation of various join summaries of SQL

<<: Summary of 76 Experience Points of User Experience

>>: Detailed explanation of common methods of JavaScript String