For the join statement of the following three tables
select * from t1 join t2 on(t1.a=t2.a) join t3 on (t2.b=t3.b) where t1.c>=X and t2.c>=Y and t3.c>=Z;
If it is rewritten as straight_join , how to specify the connection order and how to create indexes for the three tables?
- Try to use the BKA algorithm.
Using BKA is not "calculate the results of two tables join first, and then join the third table", but directly nest the query. Specific implementation: among the three conditions of t1.c>=X, t2.c>=Y, t3.c>=Z, select the table with the least data after filtering as the first driving table. At this time, the following two situations may occur.
If the table t1 or t3 is selected, the remaining part is fixed:
- If the driving table is t1, the connection sequence is t1->t2->t3, and the index must be created on the field of the driven table, that is, t2.a and t3.b.
- If the driving table is t3, the connection sequence is t3->t2->t1, and indexes need to be created on t2.b and t1.a.
At the same time, we also need to create an index on the field c of the first driving table.
The second case is that if the first selected driving table is table t2, the filtering effect of the other two conditions needs to be evaluated.
The idea is to try to make the data set of the driving table participating in the join as small as possible, because in this way our driving table will be smaller.