On the embedded Loop Operation of mysql concatenated Table the execution order of on and where in nested loop join

for example, the statement
select * from table_a join table_b on table_a.id=table_b.a_id and table_a.name="smith" join table_c where table_a.priority=3 and table_b.seq=8 and table_c.sales=1998;

in the operation of embedded loops in concatenated tables, if table_a is the driver table

1) table_a does the first layer circular set according to the result set after table_a.name="smith" Filter in the on condition, and so on. Wait until the temporary table generated by an on bmemorie c join table and then do Filter according to the condition in the where statement
2) table_a according to the table_a.name="smith" in the on condition and the result set after the where condition in the first layer circular set, and so on

actually means to understand the execution order of on and where
which is right?

Mysql
Mar.12,2021

the theoretical basis of mysql has not been found. My understanding is to implement the Filter condition of where first, and what Filter does is obviously useless.

As you can see in the execution plan in

oracle, the Filter condition is executed first (the last line in the following code). 

explain plan for SELECT * FROM tmp_t2 t2 LEFT JOIN tmp_t1  t1 ON t2.id = t1.id AND t1.good_id = 'A'

-----------------------------------------------------------------------------
| Id  | Operation          | Name   | Rows  | Bytes | Cost (%CPU)| Time     |
-----------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |        |     3 |   147 |     6   (0)| 00:00:01 |
|*  1 |  HASH JOIN         |        |     3 |   147 |     6   (0)| 00:00:01 |
|   2 |   TABLE ACCESS FULL| TMP_T2 |     3 |    60 |     3   (0)| 00:00:01 |
|*  3 |   TABLE ACCESS FULL| TMP_T1 |     3 |    87 |     3   (0)| 00:00:01 |
-----------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
   1 - access("T2"."ID"="T1"."ID")
   3 - filter("T1"."GOOD_ID"='A')

Previous: How does video.js get the resolution of the video?

Next: When Wechat ios drags the input area, the overall page can be dragged, but the page will not slide?

css mysql arrays josn react html typescript webpack npm sass R objective-c .net sql-server jquery python-3.x angularjs django angular excel regex iphone ajax linux xml pandas vba spring database wordpress string wpf xcode windows bash postgresql oracle multithreading eclipse list firebase algorithm macos forms image scala visual-studio azure bootstrap spring-boot react-native python-2.7 docker performance function winforms matlab powershell apache dataframe api sqlite numpy rest shell selenium flutter dart maven loops qt swing android-studio csv express file class tensorflow sorting codeigniter perl
© 2021 CodesHelper
Advertising Contact us About us
Menu