[MDEV-19656] MariaDB behaviour with many JOINs Created: 2019-05-31  Updated: 2023-04-27

Status: Open
Project: MariaDB Server
Component/s: Optimizer
Affects Version/s: 10.2.16, 10.3.15, 10.4.5
Fix Version/s: 10.4

Type: Bug Priority: Major
Reporter: SuperC Assignee: Igor Babaev
Resolution: Unresolved Votes: 0
Labels: optimizer
Environment:

Linux x64

Attachments: Text File data_definition.txt     Text File explain_30_join.txt     Text File explain_31_join.txt     Text File query.txt    
Issue Links:
Relates
relates to MDEV-11050 Reuse temporary tables created for ma... Stalled

 Description   

I found MariaDB behaviour changing according to the number of JOINs in a query.
In this particular application, when the number of JOINs goes from 30 to 31 the execution plan changes, causing potential problems to the execution time and resources management.
The tests were made with few records and with millions of records: apparently this behaviour doesn't depend on the number of records, but when they are millions, the chosen plan considerably slows down execution using gigabytes of temporary files and can crash entire system.
I couldn't find any configuration parameters that drive this behaviour, I don't know if it can be changed

 Comments   
Comment by Alice Sherepa [ 2019-06-04 ]

The behaviour is repeatable on 5.5-10.4, with just a few rows in tables and bigger table. (I used 10000rows just to see a difference in execution time)
igor, could you please take a look:

create table t1 (id varchar(255) not null primary key, b int, key (b));
 
insert into t1 select concat('id',seq),seq from seq_1_to_100;
 
 
 
create table t2 (id int not null primary key, name varchar(255) not null, key (name));
 
insert into t2 select seq+100,concat('f',seq) from seq_1_to_10000; 
 
 
create table t3 (b int not null, c int not null, a varchar(255), primary key (b, c), key (c), key (a));
 
insert into t3 select 1,seq+100,concat('str-1-',seq) from seq_1_to_10000;  
insert into t3 select 2,seq+100,concat('str-2-',seq) from seq_10001_to_20000;  
 
 
create table t4 (id int not null default 0 primary key);
 
insert into t4 select seq from seq_1_to_10000;
 
 
 
create view v1 as
 
  select t3.b, t3.a, t2.name from t3 left join t2 on t3.c = t2.id; 
 
 
select 1
 
from 
t1 join t4 on t1.b=t4.id 
left join v1 v1_2 on t4.id=v1_2.b and (v1_2.name='f1') 
left join v1 v1_3 on t4.id=v1_3.b and (v1_3.name='f2') 
left join v1 v1_4 on t4.id=v1_4.b and (v1_4.name='f3') 
left join v1 v1_5 on t4.id=v1_5.b and (v1_5.name='f4') 
left join v1 v1_6 on t4.id=v1_6.b and (v1_6.name='f5') 
left join v1 v1_7 on t4.id=v1_7.b and (v1_7.name='f6') 
left join v1 v1_8 on t4.id=v1_8.b and (v1_8.name='f7') 
left join v1 v1_9 on t4.id=v1_9.b and (v1_9.name='f8') 
left join v1 v1_10 on t4.id=v1_10.b and (v1_10.name='f9') 
left join v1 v1_11 on t4.id=v1_11.b and (v1_11.name='f10') 
left join v1 v1_12 on t4.id=v1_12.b and (v1_12.name='f11') 
left join v1 v1_13 on t4.id=v1_13.b and (v1_13.name='f12') 
left join v1 v1_14 on t4.id=v1_14.b and (v1_14.name='f13') 
left join v1 v1_15 on t4.id=v1_15.b and (v1_15.name='f14') 
left join v1 v1_16 on t4.id=v1_16.b and (v1_16.name='f15') 
left join v1 v1_17 on t4.id=v1_17.b and (v1_17.name='f16') 
left join v1 v1_18 on t4.id=v1_18.b and (v1_18.name='f17') 
left join v1 v1_19 on t4.id=v1_19.b and (v1_19.name='f18') 
left join v1 v1_20 on t4.id=v1_20.b and (v1_20.name='f19') 
left join v1 v1_21 on t4.id=v1_21.b and (v1_21.name='f20') 
left join v1 v1_22 on t4.id=v1_22.b and (v1_22.name='f21') 
left join v1 v1_23 on t4.id=v1_23.b and (v1_23.name='f22') 
left join v1 v1_24 on t4.id=v1_24.b and (v1_24.name='f23') 
left join v1 v1_25 on t4.id=v1_25.b and (v1_25.name='f24') 
left join v1 v1_26 on t4.id=v1_26.b and (v1_26.name='f25') 
left join v1 v1_27 on t4.id=v1_27.b and (v1_27.name='f26') 
left join v1 v1_28 on t4.id=v1_28.b and (v1_28.name='f27') 
left join v1 v1_29 on t4.id=v1_29.b and (v1_29.name='f28') 
left join v1 v1_30 on t4.id=v1_30.b and (v1_30.name='f29') 
#left join v1 v1_31 on t4.id=v1_31.b and (v1_31.name='f30') 
where t1.b in (1,2,3,4,5);
 
 
select  1
 
from 
t1 join t4 on t1.b=t4.id 
left join v1 v1_2 on t4.id=v1_2.b and (v1_2.name='f1') 
left join v1 v1_3 on t4.id=v1_3.b and (v1_3.name='f2') 
left join v1 v1_4 on t4.id=v1_4.b and (v1_4.name='f3') 
left join v1 v1_5 on t4.id=v1_5.b and (v1_5.name='f4') 
left join v1 v1_6 on t4.id=v1_6.b and (v1_6.name='f5') 
left join v1 v1_7 on t4.id=v1_7.b and (v1_7.name='f6') 
left join v1 v1_8 on t4.id=v1_8.b and (v1_8.name='f7') 
left join v1 v1_9 on t4.id=v1_9.b and (v1_9.name='f8') 
left join v1 v1_10 on t4.id=v1_10.b and (v1_10.name='f9') 
left join v1 v1_11 on t4.id=v1_11.b and (v1_11.name='f10') 
left join v1 v1_12 on t4.id=v1_12.b and (v1_12.name='f11') 
left join v1 v1_13 on t4.id=v1_13.b and (v1_13.name='f12') 
left join v1 v1_14 on t4.id=v1_14.b and (v1_14.name='f13') 
left join v1 v1_15 on t4.id=v1_15.b and (v1_15.name='f14') 
left join v1 v1_16 on t4.id=v1_16.b and (v1_16.name='f15') 
left join v1 v1_17 on t4.id=v1_17.b and (v1_17.name='f16') 
left join v1 v1_18 on t4.id=v1_18.b and (v1_18.name='f17') 
left join v1 v1_19 on t4.id=v1_19.b and (v1_19.name='f18') 
left join v1 v1_20 on t4.id=v1_20.b and (v1_20.name='f19') 
left join v1 v1_21 on t4.id=v1_21.b and (v1_21.name='f20') 
left join v1 v1_22 on t4.id=v1_22.b and (v1_22.name='f21') 
left join v1 v1_23 on t4.id=v1_23.b and (v1_23.name='f22') 
left join v1 v1_24 on t4.id=v1_24.b and (v1_24.name='f23') 
left join v1 v1_25 on t4.id=v1_25.b and (v1_25.name='f24') 
left join v1 v1_26 on t4.id=v1_26.b and (v1_26.name='f25') 
left join v1 v1_27 on t4.id=v1_27.b and (v1_27.name='f26') 
left join v1 v1_28 on t4.id=v1_28.b and (v1_28.name='f27') 
left join v1 v1_29 on t4.id=v1_29.b and (v1_29.name='f28') 
left join v1 v1_30 on t4.id=v1_30.b and (v1_30.name='f29') 
left join v1 v1_31 on t4.id=v1_31.b and (v1_31.name='f30') 
where t1.b in (1,2,3,4,5);

10.3.15

 
MariaDB [test]> select  1
 
    -> from 
    -> t1 join t4 on t1.b=t4.id 
    -> left join v1 v1_2 on t4.id=v1_2.b and (v1_2.name='f1') 
    -> left join v1 v1_3 on t4.id=v1_3.b and (v1_3.name='f2') 
    -> left join v1 v1_4 on t4.id=v1_4.b and (v1_4.name='f3') 
    -> left join v1 v1_5 on t4.id=v1_5.b and (v1_5.name='f4') 
    -> left join v1 v1_6 on t4.id=v1_6.b and (v1_6.name='f5') 
    -> left join v1 v1_7 on t4.id=v1_7.b and (v1_7.name='f6') 
    -> left join v1 v1_8 on t4.id=v1_8.b and (v1_8.name='f7') 
    -> left join v1 v1_9 on t4.id=v1_9.b and (v1_9.name='f8') 
    -> left join v1 v1_10 on t4.id=v1_10.b and (v1_10.name='f9') 
    -> left join v1 v1_11 on t4.id=v1_11.b and (v1_11.name='f10') 
    -> left join v1 v1_12 on t4.id=v1_12.b and (v1_12.name='f11') 
    -> left join v1 v1_13 on t4.id=v1_13.b and (v1_13.name='f12') 
    -> left join v1 v1_14 on t4.id=v1_14.b and (v1_14.name='f13') 
    -> left join v1 v1_15 on t4.id=v1_15.b and (v1_15.name='f14') 
    -> left join v1 v1_16 on t4.id=v1_16.b and (v1_16.name='f15') 
    -> left join v1 v1_17 on t4.id=v1_17.b and (v1_17.name='f16') 
    -> left join v1 v1_18 on t4.id=v1_18.b and (v1_18.name='f17') 
    -> left join v1 v1_19 on t4.id=v1_19.b and (v1_19.name='f18') 
    -> left join v1 v1_20 on t4.id=v1_20.b and (v1_20.name='f19') 
    -> left join v1 v1_21 on t4.id=v1_21.b and (v1_21.name='f20') 
    -> left join v1 v1_22 on t4.id=v1_22.b and (v1_22.name='f21') 
    -> left join v1 v1_23 on t4.id=v1_23.b and (v1_23.name='f22') 
    -> left join v1 v1_24 on t4.id=v1_24.b and (v1_24.name='f23') 
    -> left join v1 v1_25 on t4.id=v1_25.b and (v1_25.name='f24') 
    -> left join v1 v1_26 on t4.id=v1_26.b and (v1_26.name='f25') 
    -> left join v1 v1_27 on t4.id=v1_27.b and (v1_27.name='f26') 
    -> left join v1 v1_28 on t4.id=v1_28.b and (v1_28.name='f27') 
    -> left join v1 v1_29 on t4.id=v1_29.b and (v1_29.name='f28') 
    -> left join v1 v1_30 on t4.id=v1_30.b and (v1_30.name='f29') 
    -> #left join v1 v1_31 on t4.id=v1_31.b and (v1_31.name='f30') 
    -> where t1.b in (1,2,3,4,5);
 
+---+
 
| 1 |
 
+---+
 
| 1 |
 
| 1 |
 
| 1 |
 
| 1 |
 
| 1 |
 
+---+
 
5 rows in set (0.005 sec)
 
 
 
MariaDB [test]> select  1
 
    -> from 
    -> t1 join t4  on t1.b=t4.id 
    -> left join v1 v1_2 on t4.id=v1_2.b and (v1_2.name='f1') 
    -> left join v1 v1_3 on t4.id=v1_3.b and (v1_3.name='f2') 
    -> left join v1 v1_4 on t4.id=v1_4.b and (v1_4.name='f3') 
    -> left join v1 v1_5 on t4.id=v1_5.b and (v1_5.name='f4') 
    -> left join v1 v1_6 on t4.id=v1_6.b and (v1_6.name='f5') 
    -> left join v1 v1_7 on t4.id=v1_7.b and (v1_7.name='f6') 
    -> left join v1 v1_8 on t4.id=v1_8.b and (v1_8.name='f7') 
    -> left join v1 v1_9 on t4.id=v1_9.b and (v1_9.name='f8') 
    -> left join v1 v1_10 on t4.id=v1_10.b and (v1_10.name='f9') 
    -> left join v1 v1_11 on t4.id=v1_11.b and (v1_11.name='f10') 
    -> left join v1 v1_12 on t4.id=v1_12.b and (v1_12.name='f11') 
    -> left join v1 v1_13 on t4.id=v1_13.b and (v1_13.name='f12') 
    -> left join v1 v1_14 on t4.id=v1_14.b and (v1_14.name='f13') 
    -> left join v1 v1_15 on t4.id=v1_15.b and (v1_15.name='f14') 
    -> left join v1 v1_16 on t4.id=v1_16.b and (v1_16.name='f15') 
    -> left join v1 v1_17 on t4.id=v1_17.b and (v1_17.name='f16') 
    -> left join v1 v1_18 on t4.id=v1_18.b and (v1_18.name='f17') 
    -> left join v1 v1_19 on t4.id=v1_19.b and (v1_19.name='f18') 
    -> left join v1 v1_20 on t4.id=v1_20.b and (v1_20.name='f19') 
    -> left join v1 v1_21 on t4.id=v1_21.b and (v1_21.name='f20') 
    -> left join v1 v1_22 on t4.id=v1_22.b and (v1_22.name='f21') 
    -> left join v1 v1_23 on t4.id=v1_23.b and (v1_23.name='f22') 
    -> left join v1 v1_24 on t4.id=v1_24.b and (v1_24.name='f23') 
    -> left join v1 v1_25 on t4.id=v1_25.b and (v1_25.name='f24') 
    -> left join v1 v1_26 on t4.id=v1_26.b and (v1_26.name='f25') 
    -> left join v1 v1_27 on t4.id=v1_27.b and (v1_27.name='f26') 
    -> left join v1 v1_28 on t4.id=v1_28.b and (v1_28.name='f27') 
    -> left join v1 v1_29 on t4.id=v1_29.b and (v1_29.name='f28') 
    -> left join v1 v1_30 on t4.id=v1_30.b and (v1_30.name='f29') 
    -> left join v1 v1_31 on t4.id=v1_31.b and (v1_31.name='f30') 
    -> where t1.b in (1,2,3,4,5);
 
+---+  
| 1 |
 
+---+
 
| 1 |
 
| 1 |
 
| 1 |
 
| 1 |
 
| 1 |
 
+---+
 
5 rows in set (0.283 sec)


MariaDB [test]> explain
 
    -> select  1
 
    -> from 
    -> t1 join t4 on t1.b=t4.id 
    -> left join v1 v1_2 on t4.id=v1_2.b and (v1_2.name='f1') 
    -> left join v1 v1_3 on t4.id=v1_3.b and (v1_3.name='f2') 
    -> left join v1 v1_4 on t4.id=v1_4.b and (v1_4.name='f3') 
    -> left join v1 v1_5 on t4.id=v1_5.b and (v1_5.name='f4') 
    -> left join v1 v1_6 on t4.id=v1_6.b and (v1_6.name='f5') 
    -> left join v1 v1_7 on t4.id=v1_7.b and (v1_7.name='f6') 
    -> left join v1 v1_8 on t4.id=v1_8.b and (v1_8.name='f7') 
    -> left join v1 v1_9 on t4.id=v1_9.b and (v1_9.name='f8') 
    -> left join v1 v1_10 on t4.id=v1_10.b and (v1_10.name='f9') 
    -> left join v1 v1_11 on t4.id=v1_11.b and (v1_11.name='f10') 
    -> left join v1 v1_12 on t4.id=v1_12.b and (v1_12.name='f11') 
    -> left join v1 v1_13 on t4.id=v1_13.b and (v1_13.name='f12') 
    -> left join v1 v1_14 on t4.id=v1_14.b and (v1_14.name='f13') 
    -> left join v1 v1_15 on t4.id=v1_15.b and (v1_15.name='f14') 
    -> left join v1 v1_16 on t4.id=v1_16.b and (v1_16.name='f15') 
    -> left join v1 v1_17 on t4.id=v1_17.b and (v1_17.name='f16') 
    -> left join v1 v1_18 on t4.id=v1_18.b and (v1_18.name='f17') 
    -> left join v1 v1_19 on t4.id=v1_19.b and (v1_19.name='f18') 
    -> left join v1 v1_20 on t4.id=v1_20.b and (v1_20.name='f19') 
    -> left join v1 v1_21 on t4.id=v1_21.b and (v1_21.name='f20') 
    -> left join v1 v1_22 on t4.id=v1_22.b and (v1_22.name='f21') 
    -> left join v1 v1_23 on t4.id=v1_23.b and (v1_23.name='f22') 
    -> left join v1 v1_24 on t4.id=v1_24.b and (v1_24.name='f23') 
    -> left join v1 v1_25 on t4.id=v1_25.b and (v1_25.name='f24') 
    -> left join v1 v1_26 on t4.id=v1_26.b and (v1_26.name='f25') 
    -> left join v1 v1_27 on t4.id=v1_27.b and (v1_27.name='f26') 
    -> left join v1 v1_28 on t4.id=v1_28.b and (v1_28.name='f27') 
    -> left join v1 v1_29 on t4.id=v1_29.b and (v1_29.name='f28') 
    -> left join v1 v1_30 on t4.id=v1_30.b and (v1_30.name='f29') 
    -> #left join v1 v1_31 on t4.id=v1_31.b and (v1_31.name='f30') 
    -> where t1.b in (1,2,3,4,5);
 
+------+-------------+-------+--------+---------------+---------+---------+----------------------+------+--------------------------+
 
| id   | select_type | table | type   | possible_keys | key     | key_len | ref                  | rows | Extra                    |
 
+------+-------------+-------+--------+---------------+---------+---------+----------------------+------+--------------------------+
 
|    1 | SIMPLE      | t1    | range  | b             | b       | 5       | NULL                 |    5 | Using where; Using index |
 
|    1 | SIMPLE      | t4    | eq_ref | PRIMARY       | PRIMARY | 4       | test.t1.b            |    1 | Using index              |
 
|    1 | SIMPLE      | t2    | ref    | PRIMARY,name  | name    | 257     | const                |    1 | Using where; Using index |
 
|    1 | SIMPLE      | t3    | eq_ref | PRIMARY,c     | PRIMARY | 8       | test.t1.b,test.t2.id |    1 | Using index              |
 
|    1 | SIMPLE      | t2    | ref    | PRIMARY,name  | name    | 257     | const                |    1 | Using where; Using index |
 
|    1 | SIMPLE      | t3    | eq_ref | PRIMARY,c     | PRIMARY | 8       | test.t1.b,test.t2.id |    1 | Using index              |
 
|    1 | SIMPLE      | t2    | ref    | PRIMARY,name  | name    | 257     | const                |    1 | Using where; Using index |
 
|    1 | SIMPLE      | t3    | eq_ref | PRIMARY,c     | PRIMARY | 8       | test.t1.b,test.t2.id |    1 | Using index              |
 
|    1 | SIMPLE      | t2    | ref    | PRIMARY,name  | name    | 257     | const                |    1 | Using where; Using index |
 
|    1 | SIMPLE      | t3    | eq_ref | PRIMARY,c     | PRIMARY | 8       | test.t1.b,test.t2.id |    1 | Using index              |
 
|    1 | SIMPLE      | t2    | ref    | PRIMARY,name  | name    | 257     | const                |    1 | Using where; Using index |
 
|    1 | SIMPLE      | t3    | eq_ref | PRIMARY,c     | PRIMARY | 8       | test.t1.b,test.t2.id |    1 | Using index              |
 
|    1 | SIMPLE      | t2    | ref    | PRIMARY,name  | name    | 257     | const                |    1 | Using where; Using index |
 
|    1 | SIMPLE      | t3    | eq_ref | PRIMARY,c     | PRIMARY | 8       | test.t1.b,test.t2.id |    1 | Using index              |
 
|    1 | SIMPLE      | t2    | ref    | PRIMARY,name  | name    | 257     | const                |    1 | Using where; Using index |
 
|    1 | SIMPLE      | t3    | eq_ref | PRIMARY,c     | PRIMARY | 8       | test.t1.b,test.t2.id |    1 | Using index              |
 
|    1 | SIMPLE      | t2    | ref    | PRIMARY,name  | name    | 257     | const                |    1 | Using where; Using index |
 
|    1 | SIMPLE      | t3    | eq_ref | PRIMARY,c     | PRIMARY | 8       | test.t1.b,test.t2.id |    1 | Using index              |
 
|    1 | SIMPLE      | t2    | ref    | PRIMARY,name  | name    | 257     | const                |    1 | Using where; Using index |
 
|    1 | SIMPLE      | t3    | eq_ref | PRIMARY,c     | PRIMARY | 8       | test.t1.b,test.t2.id |    1 | Using index              |
 
|    1 | SIMPLE      | t2    | ref    | PRIMARY,name  | name    | 257     | const                |    1 | Using where; Using index |
 
|    1 | SIMPLE      | t3    | eq_ref | PRIMARY,c     | PRIMARY | 8       | test.t1.b,test.t2.id |    1 | Using index              |
 
|    1 | SIMPLE      | t2    | ref    | PRIMARY,name  | name    | 257     | const                |    1 | Using where; Using index |
 
|    1 | SIMPLE      | t3    | eq_ref | PRIMARY,c     | PRIMARY | 8       | test.t1.b,test.t2.id |    1 | Using index              |
 
|    1 | SIMPLE      | t2    | ref    | PRIMARY,name  | name    | 257     | const                |    1 | Using where; Using index |
 
|    1 | SIMPLE      | t3    | eq_ref | PRIMARY,c     | PRIMARY | 8       | test.t1.b,test.t2.id |    1 | Using index              |
 
|    1 | SIMPLE      | t2    | ref    | PRIMARY,name  | name    | 257     | const                |    1 | Using where; Using index |
 
|    1 | SIMPLE      | t3    | eq_ref | PRIMARY,c     | PRIMARY | 8       | test.t1.b,test.t2.id |    1 | Using index              |
 
|    1 | SIMPLE      | t2    | ref    | PRIMARY,name  | name    | 257     | const                |    1 | Using where; Using index |
 
|    1 | SIMPLE      | t3    | eq_ref | PRIMARY,c     | PRIMARY | 8       | test.t1.b,test.t2.id |    1 | Using index              |
 
|    1 | SIMPLE      | t2    | ref    | PRIMARY,name  | name    | 257     | const                |    1 | Using where; Using index |
 
|    1 | SIMPLE      | t3    | eq_ref | PRIMARY,c     | PRIMARY | 8       | test.t1.b,test.t2.id |    1 | Using index              |
 
|    1 | SIMPLE      | t2    | ref    | PRIMARY,name  | name    | 257     | const                |    1 | Using where; Using index |
 
|    1 | SIMPLE      | t3    | eq_ref | PRIMARY,c     | PRIMARY | 8       | test.t1.b,test.t2.id |    1 | Using index              |
 
|    1 | SIMPLE      | t2    | ref    | PRIMARY,name  | name    | 257     | const                |    1 | Using where; Using index |
 
|    1 | SIMPLE      | t3    | eq_ref | PRIMARY,c     | PRIMARY | 8       | test.t1.b,test.t2.id |    1 | Using index              |
 
|    1 | SIMPLE      | t2    | ref    | PRIMARY,name  | name    | 257     | const                |    1 | Using where; Using index |
 
|    1 | SIMPLE      | t3    | eq_ref | PRIMARY,c     | PRIMARY | 8       | test.t1.b,test.t2.id |    1 | Using index              |
 
|    1 | SIMPLE      | t2    | ref    | PRIMARY,name  | name    | 257     | const                |    1 | Using where; Using index |
 
|    1 | SIMPLE      | t3    | eq_ref | PRIMARY,c     | PRIMARY | 8       | test.t1.b,test.t2.id |    1 | Using index              |
 
|    1 | SIMPLE      | t2    | ref    | PRIMARY,name  | name    | 257     | const                |    1 | Using where; Using index |
 
|    1 | SIMPLE      | t3    | eq_ref | PRIMARY,c     | PRIMARY | 8       | test.t1.b,test.t2.id |    1 | Using index              |
 
|    1 | SIMPLE      | t2    | ref    | PRIMARY,name  | name    | 257     | const                |    1 | Using where; Using index |
 
|    1 | SIMPLE      | t3    | eq_ref | PRIMARY,c     | PRIMARY | 8       | test.t1.b,test.t2.id |    1 | Using index              |
 
|    1 | SIMPLE      | t2    | ref    | PRIMARY,name  | name    | 257     | const                |    1 | Using where; Using index |
 
|    1 | SIMPLE      | t3    | eq_ref | PRIMARY,c     | PRIMARY | 8       | test.t1.b,test.t2.id |    1 | Using index              |
 
|    1 | SIMPLE      | t2    | ref    | PRIMARY,name  | name    | 257     | const                |    1 | Using where; Using index |
 
|    1 | SIMPLE      | t3    | eq_ref | PRIMARY,c     | PRIMARY | 8       | test.t1.b,test.t2.id |    1 | Using index              |
 
|    1 | SIMPLE      | t2    | ref    | PRIMARY,name  | name    | 257     | const                |    1 | Using where; Using index |
 
|    1 | SIMPLE      | t3    | eq_ref | PRIMARY,c     | PRIMARY | 8       | test.t1.b,test.t2.id |    1 | Using index              |
 
|    1 | SIMPLE      | t2    | ref    | PRIMARY,name  | name    | 257     | const                |    1 | Using where; Using index |
 
|    1 | SIMPLE      | t3    | eq_ref | PRIMARY,c     | PRIMARY | 8       | test.t1.b,test.t2.id |    1 | Using index              |
 
|    1 | SIMPLE      | t2    | ref    | PRIMARY,name  | name    | 257     | const                |    1 | Using where; Using index |
 
|    1 | SIMPLE      | t3    | eq_ref | PRIMARY,c     | PRIMARY | 8       | test.t1.b,test.t2.id |    1 | Using index              |
 
|    1 | SIMPLE      | t2    | ref    | PRIMARY,name  | name    | 257     | const                |    1 | Using where; Using index |
 
|    1 | SIMPLE      | t3    | eq_ref | PRIMARY,c     | PRIMARY | 8       | test.t1.b,test.t2.id |    1 | Using index              |
 
|    1 | SIMPLE      | t2    | ref    | PRIMARY,name  | name    | 257     | const                |    1 | Using where; Using index |
 
|    1 | SIMPLE      | t3    | eq_ref | PRIMARY,c     | PRIMARY | 8       | test.t1.b,test.t2.id |    1 | Using index              |
 
|    1 | SIMPLE      | t2    | ref    | PRIMARY,name  | name    | 257     | const                |    1 | Using where; Using index |
 
|    1 | SIMPLE      | t3    | eq_ref | PRIMARY,c     | PRIMARY | 8       | test.t1.b,test.t2.id |    1 | Using index              |
 
+------+-------------+-------+--------+---------------+---------+---------+----------------------+------+--------------------------+
 
60 rows in set (0.002 sec)


MariaDB [test]> explain 
    -> select  1
 
    -> from 
    -> t1 join t4 on t1.b=t4.id 
    -> left join v1 v1_2 on t4.id=v1_2.b and (v1_2.name='f1') 
    -> left join v1 v1_3 on t4.id=v1_3.b and (v1_3.name='f2') 
    -> left join v1 v1_4 on t4.id=v1_4.b and (v1_4.name='f3') 
    -> left join v1 v1_5 on t4.id=v1_5.b and (v1_5.name='f4') 
    -> left join v1 v1_6 on t4.id=v1_6.b and (v1_6.name='f5') 
    -> left join v1 v1_7 on t4.id=v1_7.b and (v1_7.name='f6') 
    -> left join v1 v1_8 on t4.id=v1_8.b and (v1_8.name='f7') 
    -> left join v1 v1_9 on t4.id=v1_9.b and (v1_9.name='f8') 
    -> left join v1 v1_10 on t4.id=v1_10.b and (v1_10.name='f9') 
    -> left join v1 v1_11 on t4.id=v1_11.b and (v1_11.name='f10') 
    -> left join v1 v1_12 on t4.id=v1_12.b and (v1_12.name='f11') 
    -> left join v1 v1_13 on t4.id=v1_13.b and (v1_13.name='f12') 
    -> left join v1 v1_14 on t4.id=v1_14.b and (v1_14.name='f13') 
    -> left join v1 v1_15 on t4.id=v1_15.b and (v1_15.name='f14') 
    -> left join v1 v1_16 on t4.id=v1_16.b and (v1_16.name='f15') 
    -> left join v1 v1_17 on t4.id=v1_17.b and (v1_17.name='f16') 
    -> left join v1 v1_18 on t4.id=v1_18.b and (v1_18.name='f17') 
    -> left join v1 v1_19 on t4.id=v1_19.b and (v1_19.name='f18') 
    -> left join v1 v1_20 on t4.id=v1_20.b and (v1_20.name='f19') 
    -> left join v1 v1_21 on t4.id=v1_21.b and (v1_21.name='f20') 
    -> left join v1 v1_22 on t4.id=v1_22.b and (v1_22.name='f21') 
    -> left join v1 v1_23 on t4.id=v1_23.b and (v1_23.name='f22') 
    -> left join v1 v1_24 on t4.id=v1_24.b and (v1_24.name='f23') 
    -> left join v1 v1_25 on t4.id=v1_25.b and (v1_25.name='f24') 
    -> left join v1 v1_26 on t4.id=v1_26.b and (v1_26.name='f25') 
    -> left join v1 v1_27 on t4.id=v1_27.b and (v1_27.name='f26') 
    -> left join v1 v1_28 on t4.id=v1_28.b and (v1_28.name='f27') 
    -> left join v1 v1_29 on t4.id=v1_29.b and (v1_29.name='f28') 
    -> left join v1 v1_30 on t4.id=v1_30.b and (v1_30.name='f29') 
    -> left join v1 v1_31 on t4.id=v1_31.b and (v1_31.name='f30') 
    -> where t1.b in (1,2,3,4,5);
 
+------+-------------+-------------+--------+---------------+---------+---------+----------------------+-------+--------------------------------------------------------+
 
| id   | select_type | table       | type   | possible_keys | key     | key_len | ref                  | rows  | Extra                                                  |
 
+------+-------------+-------------+--------+---------------+---------+---------+----------------------+-------+--------------------------------------------------------+
 
|    1 | PRIMARY     | t1          | range  | b             | b       | 5       | NULL                 |     5 | Using where; Using index                               |
 
|    1 | PRIMARY     | t4          | eq_ref | PRIMARY       | PRIMARY | 4       | test.t1.b            |     1 | Using index                                            |
 
|    1 | PRIMARY     | t2          | ref    | PRIMARY,name  | name    | 257     | const                |     1 | Using where; Using index                               |
 
|    1 | PRIMARY     | t3          | eq_ref | PRIMARY,c     | PRIMARY | 8       | test.t1.b,test.t2.id |     1 | Using index                                            |
 
|    1 | PRIMARY     | t2          | ref    | PRIMARY,name  | name    | 257     | const                |     1 | Using where; Using index                               |
 
|    1 | PRIMARY     | t3          | eq_ref | PRIMARY,c     | PRIMARY | 8       | test.t1.b,test.t2.id |     1 | Using index                                            |
 
|    1 | PRIMARY     | t2          | ref    | PRIMARY,name  | name    | 257     | const                |     1 | Using where; Using index                               |
 
|    1 | PRIMARY     | t3          | eq_ref | PRIMARY,c     | PRIMARY | 8       | test.t1.b,test.t2.id |     1 | Using index                                            |
 
|    1 | PRIMARY     | t2          | ref    | PRIMARY,name  | name    | 257     | const                |     1 | Using where; Using index                               |
 
|    1 | PRIMARY     | t3          | eq_ref | PRIMARY,c     | PRIMARY | 8       | test.t1.b,test.t2.id |     1 | Using index                                            |
 
|    1 | PRIMARY     | t2          | ref    | PRIMARY,name  | name    | 257     | const                |     1 | Using where; Using index                               |
 
|    1 | PRIMARY     | t3          | eq_ref | PRIMARY,c     | PRIMARY | 8       | test.t1.b,test.t2.id |     1 | Using index                                            |
 
|    1 | PRIMARY     | t2          | ref    | PRIMARY,name  | name    | 257     | const                |     1 | Using where; Using index                               |
 
|    1 | PRIMARY     | t3          | eq_ref | PRIMARY,c     | PRIMARY | 8       | test.t1.b,test.t2.id |     1 | Using index                                            |
 
|    1 | PRIMARY     | t2          | ref    | PRIMARY,name  | name    | 257     | const                |     1 | Using where; Using index                               |
 
|    1 | PRIMARY     | t3          | eq_ref | PRIMARY,c     | PRIMARY | 8       | test.t1.b,test.t2.id |     1 | Using index                                            |
 
|    1 | PRIMARY     | t2          | ref    | PRIMARY,name  | name    | 257     | const                |     1 | Using where; Using index                               |
 
|    1 | PRIMARY     | t3          | eq_ref | PRIMARY,c     | PRIMARY | 8       | test.t1.b,test.t2.id |     1 | Using index                                            |
 
|    1 | PRIMARY     | t2          | ref    | PRIMARY,name  | name    | 257     | const                |     1 | Using where; Using index                               |
 
|    1 | PRIMARY     | t3          | eq_ref | PRIMARY,c     | PRIMARY | 8       | test.t1.b,test.t2.id |     1 | Using index                                            |
 
|    1 | PRIMARY     | t2          | ref    | PRIMARY,name  | name    | 257     | const                |     1 | Using where; Using index                               |
 
|    1 | PRIMARY     | t3          | eq_ref | PRIMARY,c     | PRIMARY | 8       | test.t1.b,test.t2.id |     1 | Using index                                            |
 
|    1 | PRIMARY     | t2          | ref    | PRIMARY,name  | name    | 257     | const                |     1 | Using where; Using index                               |
 
|    1 | PRIMARY     | t3          | eq_ref | PRIMARY,c     | PRIMARY | 8       | test.t1.b,test.t2.id |     1 | Using index                                            |
 
|    1 | PRIMARY     | t2          | ref    | PRIMARY,name  | name    | 257     | const                |     1 | Using where; Using index                               |
 
|    1 | PRIMARY     | t3          | eq_ref | PRIMARY,c     | PRIMARY | 8       | test.t1.b,test.t2.id |     1 | Using index                                            |
 
|    1 | PRIMARY     | t2          | ref    | PRIMARY,name  | name    | 257     | const                |     1 | Using where; Using index                               |
 
|    1 | PRIMARY     | t3          | eq_ref | PRIMARY,c     | PRIMARY | 8       | test.t1.b,test.t2.id |     1 | Using index                                            |
 
|    1 | PRIMARY     | t2          | ref    | PRIMARY,name  | name    | 257     | const                |     1 | Using where; Using index                               |
 
|    1 | PRIMARY     | t3          | eq_ref | PRIMARY,c     | PRIMARY | 8       | test.t1.b,test.t2.id |     1 | Using index                                            |
 
|    1 | PRIMARY     | t2          | ref    | PRIMARY,name  | name    | 257     | const                |     1 | Using where; Using index                               |
 
|    1 | PRIMARY     | t3          | eq_ref | PRIMARY,c     | PRIMARY | 8       | test.t1.b,test.t2.id |     1 | Using index                                            |
 
|    1 | PRIMARY     | t2          | ref    | PRIMARY,name  | name    | 257     | const                |     1 | Using where; Using index                               |
 
|    1 | PRIMARY     | t3          | eq_ref | PRIMARY,c     | PRIMARY | 8       | test.t1.b,test.t2.id |     1 | Using index                                            |
 
|    1 | PRIMARY     | t2          | ref    | PRIMARY,name  | name    | 257     | const                |     1 | Using where; Using index                               |
 
|    1 | PRIMARY     | t3          | eq_ref | PRIMARY,c     | PRIMARY | 8       | test.t1.b,test.t2.id |     1 | Using index                                            |
 
|    1 | PRIMARY     | t2          | ref    | PRIMARY,name  | name    | 257     | const                |     1 | Using where; Using index                               |
 
|    1 | PRIMARY     | t3          | eq_ref | PRIMARY,c     | PRIMARY | 8       | test.t1.b,test.t2.id |     1 | Using index                                            |
 
|    1 | PRIMARY     | t2          | ref    | PRIMARY,name  | name    | 257     | const                |     1 | Using where; Using index                               |
 
|    1 | PRIMARY     | t3          | eq_ref | PRIMARY,c     | PRIMARY | 8       | test.t1.b,test.t2.id |     1 | Using index                                            |
 
|    1 | PRIMARY     | t2          | ref    | PRIMARY,name  | name    | 257     | const                |     1 | Using where; Using index                               |
 
|    1 | PRIMARY     | t3          | eq_ref | PRIMARY,c     | PRIMARY | 8       | test.t1.b,test.t2.id |     1 | Using index                                            |
 
|    1 | PRIMARY     | t2          | ref    | PRIMARY,name  | name    | 257     | const                |     1 | Using where; Using index                               |
 
|    1 | PRIMARY     | t3          | eq_ref | PRIMARY,c     | PRIMARY | 8       | test.t1.b,test.t2.id |     1 | Using index                                            |
 
|    1 | PRIMARY     | t2          | ref    | PRIMARY,name  | name    | 257     | const                |     1 | Using where; Using index                               |
 
|    1 | PRIMARY     | t3          | eq_ref | PRIMARY,c     | PRIMARY | 8       | test.t1.b,test.t2.id |     1 | Using index                                            |
 
|    1 | PRIMARY     | t2          | ref    | PRIMARY,name  | name    | 257     | const                |     1 | Using where; Using index                               |
 
|    1 | PRIMARY     | t3          | eq_ref | PRIMARY,c     | PRIMARY | 8       | test.t1.b,test.t2.id |     1 | Using index                                            |
 
|    1 | PRIMARY     | t2          | ref    | PRIMARY,name  | name    | 257     | const                |     1 | Using where; Using index                               |
 
|    1 | PRIMARY     | t3          | eq_ref | PRIMARY,c     | PRIMARY | 8       | test.t1.b,test.t2.id |     1 | Using index                                            |
 
|    1 | PRIMARY     | t2          | ref    | PRIMARY,name  | name    | 257     | const                |     1 | Using where; Using index                               |
 
|    1 | PRIMARY     | t3          | eq_ref | PRIMARY,c     | PRIMARY | 8       | test.t1.b,test.t2.id |     1 | Using index                                            |
 
|    1 | PRIMARY     | t2          | ref    | PRIMARY,name  | name    | 257     | const                |     1 | Using where; Using index                               |
 
|    1 | PRIMARY     | t3          | eq_ref | PRIMARY,c     | PRIMARY | 8       | test.t1.b,test.t2.id |     1 | Using index                                            |
 
|    1 | PRIMARY     | t2          | ref    | PRIMARY,name  | name    | 257     | const                |     1 | Using where; Using index                               |
 
|    1 | PRIMARY     | t3          | eq_ref | PRIMARY,c     | PRIMARY | 8       | test.t1.b,test.t2.id |     1 | Using index                                            |
 
|    1 | PRIMARY     | t2          | ref    | PRIMARY,name  | name    | 257     | const                |     1 | Using where; Using index                               |
 
|    1 | PRIMARY     | t3          | eq_ref | PRIMARY,c     | PRIMARY | 8       | test.t1.b,test.t2.id |     1 | Using index                                            |
 
|    1 | PRIMARY     | <derived30> | ALL    | NULL          | NULL    | NULL    | NULL                 | 19731 | Using where; Using join buffer (flat, BNL join)        |
 
|    1 | PRIMARY     | <derived31> | ALL    | NULL          | NULL    | NULL    | NULL                 | 19731 | Using where; Using join buffer (incremental, BNL join) |
 
|   31 | SUBQUERY    | t3          | index  | NULL          | a       | 258     | NULL                 | 19731 | Using index                                            |
 
|   31 | SUBQUERY    | t2          | eq_ref | PRIMARY       | PRIMARY | 4       | test.t3.c            |     1 |                                                        |
 
|   30 | SUBQUERY    | t3          | index  | NULL          | a       | 258     | NULL                 | 19731 | Using index                                            |
 
|   30 | SUBQUERY    | t2          | eq_ref | PRIMARY       | PRIMARY | 4       | test.t3.c            |     1 |                                                        |
 
+------+-------------+-------------+--------+---------------+---------+---------+----------------------+-------+--------------------------------------------------------+
 
64 rows in set (0.007 sec)


MariaDB [test]> select straight_join 1
 
    -> from 
    -> t1 join t4 on t1.b=t4.id 
    -> left join v1 v1_2 on t4.id=v1_2.b and (v1_2.name='f1') 
    -> left join v1 v1_3 on t4.id=v1_3.b and (v1_3.name='f2') 
    -> left join v1 v1_4 on t4.id=v1_4.b and (v1_4.name='f3') 
    -> left join v1 v1_5 on t4.id=v1_5.b and (v1_5.name='f4') 
    -> left join v1 v1_6 on t4.id=v1_6.b and (v1_6.name='f5') 
    -> left join v1 v1_7 on t4.id=v1_7.b and (v1_7.name='f6') 
    -> left join v1 v1_8 on t4.id=v1_8.b and (v1_8.name='f7') 
    -> left join v1 v1_9 on t4.id=v1_9.b and (v1_9.name='f8') 
    -> left join v1 v1_10 on t4.id=v1_10.b and (v1_10.name='f9') 
    -> left join v1 v1_11 on t4.id=v1_11.b and (v1_11.name='f10') 
    -> left join v1 v1_12 on t4.id=v1_12.b and (v1_12.name='f11') 
    -> left join v1 v1_13 on t4.id=v1_13.b and (v1_13.name='f12') 
    -> left join v1 v1_14 on t4.id=v1_14.b and (v1_14.name='f13') 
    -> left join v1 v1_15 on t4.id=v1_15.b and (v1_15.name='f14') 
    -> left join v1 v1_16 on t4.id=v1_16.b and (v1_16.name='f15') 
    -> left join v1 v1_17 on t4.id=v1_17.b and (v1_17.name='f16') 
    -> left join v1 v1_18 on t4.id=v1_18.b and (v1_18.name='f17') 
    -> left join v1 v1_19 on t4.id=v1_19.b and (v1_19.name='f18') 
    -> left join v1 v1_20 on t4.id=v1_20.b and (v1_20.name='f19') 
    -> left join v1 v1_21 on t4.id=v1_21.b and (v1_21.name='f20') 
    -> left join v1 v1_22 on t4.id=v1_22.b and (v1_22.name='f21') 
    -> left join v1 v1_23 on t4.id=v1_23.b and (v1_23.name='f22') 
    -> left join v1 v1_24 on t4.id=v1_24.b and (v1_24.name='f23') 
    -> left join v1 v1_25 on t4.id=v1_25.b and (v1_25.name='f24') 
    -> left join v1 v1_26 on t4.id=v1_26.b and (v1_26.name='f25') 
    -> left join v1 v1_27 on t4.id=v1_27.b and (v1_27.name='f26') 
    -> left join v1 v1_28 on t4.id=v1_28.b and (v1_28.name='f27') 
    -> left join v1 v1_29 on t4.id=v1_29.b and (v1_29.name='f28') 
    -> left join v1 v1_30 on t4.id=v1_30.b and (v1_30.name='f29') 
    -> #left join v1 v1_31 on t4.id=v1_31.b and (v1_31.name='f30') 
    -> where t1.b in (1,2,3,4,5);
 
+---+
 
| 1 |
 
+---+
 
| 1 |
 
| 1 |
 
| 1 |
 
| 1 |
 
| 1 |
 
+---+
 
5 rows in set (0.808 sec)


MariaDB [test]> set join_cache_level=0;  
Query OK, 0 rows affected (0.000 sec)
 
 
 
MariaDB [test]> select  1
 
    -> from 
    -> t1 join t4 on t1.b=t4.id 
    -> left join v1 v1_2 on t4.id=v1_2.b and (v1_2.name='f1') 
    -> left join v1 v1_3 on t4.id=v1_3.b and (v1_3.name='f2') 
    -> left join v1 v1_4 on t4.id=v1_4.b and (v1_4.name='f3') 
    -> left join v1 v1_5 on t4.id=v1_5.b and (v1_5.name='f4') 
    -> left join v1 v1_6 on t4.id=v1_6.b and (v1_6.name='f5') 
    -> left join v1 v1_7 on t4.id=v1_7.b and (v1_7.name='f6') 
    -> left join v1 v1_8 on t4.id=v1_8.b and (v1_8.name='f7') 
    -> left join v1 v1_9 on t4.id=v1_9.b and (v1_9.name='f8') 
    -> left join v1 v1_10 on t4.id=v1_10.b and (v1_10.name='f9') 
    -> left join v1 v1_11 on t4.id=v1_11.b and (v1_11.name='f10') 
    -> left join v1 v1_12 on t4.id=v1_12.b and (v1_12.name='f11') 
    -> left join v1 v1_13 on t4.id=v1_13.b and (v1_13.name='f12') 
    -> left join v1 v1_14 on t4.id=v1_14.b and (v1_14.name='f13') 
    -> left join v1 v1_15 on t4.id=v1_15.b and (v1_15.name='f14') 
    -> left join v1 v1_16 on t4.id=v1_16.b and (v1_16.name='f15') 
    -> left join v1 v1_17 on t4.id=v1_17.b and (v1_17.name='f16') 
    -> left join v1 v1_18 on t4.id=v1_18.b and (v1_18.name='f17') 
    -> left join v1 v1_19 on t4.id=v1_19.b and (v1_19.name='f18') 
    -> left join v1 v1_20 on t4.id=v1_20.b and (v1_20.name='f19') 
    -> left join v1 v1_21 on t4.id=v1_21.b and (v1_21.name='f20') 
    -> left join v1 v1_22 on t4.id=v1_22.b and (v1_22.name='f21') 
    -> left join v1 v1_23 on t4.id=v1_23.b and (v1_23.name='f22') 
    -> left join v1 v1_24 on t4.id=v1_24.b and (v1_24.name='f23') 
    -> left join v1 v1_25 on t4.id=v1_25.b and (v1_25.name='f24') 
    -> left join v1 v1_26 on t4.id=v1_26.b and (v1_26.name='f25') 
    -> left join v1 v1_27 on t4.id=v1_27.b and (v1_27.name='f26') 
    -> left join v1 v1_28 on t4.id=v1_28.b and (v1_28.name='f27') 
    -> left join v1 v1_29 on t4.id=v1_29.b and (v1_29.name='f28') 
    -> left join v1 v1_30 on t4.id=v1_30.b and (v1_30.name='f29') 
    -> left join v1 v1_31 on t4.id=v1_31.b and (v1_31.name='f30') 
    -> where t1.b in (1,2,3,4,5);
 
+---+
 
| 1 |
 
+---+
 
| 1 |
 
| 1 |
 
| 1 |
 
| 1 |
 
| 1 |
 
+---+
 
5 rows in set (0.110 sec)
 
 
 
MariaDB [test]> explain 
    -> select  1
 
    -> from 
    -> t1 join t4 on t1.b=t4.id 
    -> left join v1 v1_2 on t4.id=v1_2.b and (v1_2.name='f1') 
    -> left join v1 v1_3 on t4.id=v1_3.b and (v1_3.name='f2') 
    -> left join v1 v1_4 on t4.id=v1_4.b and (v1_4.name='f3') 
    -> left join v1 v1_5 on t4.id=v1_5.b and (v1_5.name='f4') 
    -> left join v1 v1_6 on t4.id=v1_6.b and (v1_6.name='f5') 
    -> left join v1 v1_7 on t4.id=v1_7.b and (v1_7.name='f6') 
    -> left join v1 v1_8 on t4.id=v1_8.b and (v1_8.name='f7') 
    -> left join v1 v1_9 on t4.id=v1_9.b and (v1_9.name='f8') 
    -> left join v1 v1_10 on t4.id=v1_10.b and (v1_10.name='f9') 
    -> left join v1 v1_11 on t4.id=v1_11.b and (v1_11.name='f10') 
    -> left join v1 v1_12 on t4.id=v1_12.b and (v1_12.name='f11') 
    -> left join v1 v1_13 on t4.id=v1_13.b and (v1_13.name='f12') 
    -> left join v1 v1_14 on t4.id=v1_14.b and (v1_14.name='f13') 
    -> left join v1 v1_15 on t4.id=v1_15.b and (v1_15.name='f14') 
    -> left join v1 v1_16 on t4.id=v1_16.b and (v1_16.name='f15') 
    -> left join v1 v1_17 on t4.id=v1_17.b and (v1_17.name='f16') 
    -> left join v1 v1_18 on t4.id=v1_18.b and (v1_18.name='f17') 
    -> left join v1 v1_19 on t4.id=v1_19.b and (v1_19.name='f18') 
    -> left join v1 v1_20 on t4.id=v1_20.b and (v1_20.name='f19') 
    -> left join v1 v1_21 on t4.id=v1_21.b and (v1_21.name='f20') 
    -> left join v1 v1_22 on t4.id=v1_22.b and (v1_22.name='f21') 
    -> left join v1 v1_23 on t4.id=v1_23.b and (v1_23.name='f22') 
    -> left join v1 v1_24 on t4.id=v1_24.b and (v1_24.name='f23') 
    -> left join v1 v1_25 on t4.id=v1_25.b and (v1_25.name='f24') 
    -> left join v1 v1_26 on t4.id=v1_26.b and (v1_26.name='f25') 
    -> left join v1 v1_27 on t4.id=v1_27.b and (v1_27.name='f26') 
    -> left join v1 v1_28 on t4.id=v1_28.b and (v1_28.name='f27') 
    -> left join v1 v1_29 on t4.id=v1_29.b and (v1_29.name='f28') 
    -> left join v1 v1_30 on t4.id=v1_30.b and (v1_30.name='f29') 
    -> left join v1 v1_31 on t4.id=v1_31.b and (v1_31.name='f30') 
    -> where t1.b in (1,2,3,4,5);
 
+------+-------------+-------------+--------+---------------+---------+---------+----------------------+-------+--------------------------+
 
| id   | select_type | table       | type   | possible_keys | key     | key_len | ref                  | rows  | Extra                    |
 
+------+-------------+-------------+--------+---------------+---------+---------+----------------------+-------+--------------------------+
 
|    1 | PRIMARY     | t1          | range  | b             | b       | 5       | NULL                 |     5 | Using where; Using index |
 
|    1 | PRIMARY     | t4          | eq_ref | PRIMARY       | PRIMARY | 4       | test.t1.b            |     1 | Using index              |
 
|    1 | PRIMARY     | t2          | ref    | PRIMARY,name  | name    | 257     | const                |     1 | Using where; Using index |
 
|    1 | PRIMARY     | t3          | eq_ref | PRIMARY,c     | PRIMARY | 8       | test.t1.b,test.t2.id |     1 | Using index              |
 
|    1 | PRIMARY     | t2          | ref    | PRIMARY,name  | name    | 257     | const                |     1 | Using where; Using index |
 
|    1 | PRIMARY     | t3          | eq_ref | PRIMARY,c     | PRIMARY | 8       | test.t1.b,test.t2.id |     1 | Using index              |
 
|    1 | PRIMARY     | t2          | ref    | PRIMARY,name  | name    | 257     | const                |     1 | Using where; Using index |
 
|    1 | PRIMARY     | t3          | eq_ref | PRIMARY,c     | PRIMARY | 8       | test.t1.b,test.t2.id |     1 | Using index              |
 
|    1 | PRIMARY     | t2          | ref    | PRIMARY,name  | name    | 257     | const                |     1 | Using where; Using index |
 
|    1 | PRIMARY     | t3          | eq_ref | PRIMARY,c     | PRIMARY | 8       | test.t1.b,test.t2.id |     1 | Using index              |
 
|    1 | PRIMARY     | t2          | ref    | PRIMARY,name  | name    | 257     | const                |     1 | Using where; Using index |
 
|    1 | PRIMARY     | t3          | eq_ref | PRIMARY,c     | PRIMARY | 8       | test.t1.b,test.t2.id |     1 | Using index              |
 
|    1 | PRIMARY     | t2          | ref    | PRIMARY,name  | name    | 257     | const                |     1 | Using where; Using index |
 
|    1 | PRIMARY     | t3          | eq_ref | PRIMARY,c     | PRIMARY | 8       | test.t1.b,test.t2.id |     1 | Using index              |
 
|    1 | PRIMARY     | t2          | ref    | PRIMARY,name  | name    | 257     | const                |     1 | Using where; Using index |
 
|    1 | PRIMARY     | t3          | eq_ref | PRIMARY,c     | PRIMARY | 8       | test.t1.b,test.t2.id |     1 | Using index              |
 
|    1 | PRIMARY     | t2          | ref    | PRIMARY,name  | name    | 257     | const                |     1 | Using where; Using index |
 
|    1 | PRIMARY     | t3          | eq_ref | PRIMARY,c     | PRIMARY | 8       | test.t1.b,test.t2.id |     1 | Using index              |
 
|    1 | PRIMARY     | t2          | ref    | PRIMARY,name  | name    | 257     | const                |     1 | Using where; Using index |
 
|    1 | PRIMARY     | t3          | eq_ref | PRIMARY,c     | PRIMARY | 8       | test.t1.b,test.t2.id |     1 | Using index              |
 
|    1 | PRIMARY     | t2          | ref    | PRIMARY,name  | name    | 257     | const                |     1 | Using where; Using index |
 
|    1 | PRIMARY     | t3          | eq_ref | PRIMARY,c     | PRIMARY | 8       | test.t1.b,test.t2.id |     1 | Using index              |
 
|    1 | PRIMARY     | t2          | ref    | PRIMARY,name  | name    | 257     | const                |     1 | Using where; Using index |
 
|    1 | PRIMARY     | t3          | eq_ref | PRIMARY,c     | PRIMARY | 8       | test.t1.b,test.t2.id |     1 | Using index              |
 
|    1 | PRIMARY     | t2          | ref    | PRIMARY,name  | name    | 257     | const                |     1 | Using where; Using index |
 
|    1 | PRIMARY     | t3          | eq_ref | PRIMARY,c     | PRIMARY | 8       | test.t1.b,test.t2.id |     1 | Using index              |
 
|    1 | PRIMARY     | t2          | ref    | PRIMARY,name  | name    | 257     | const                |     1 | Using where; Using index |
 
|    1 | PRIMARY     | t3          | eq_ref | PRIMARY,c     | PRIMARY | 8       | test.t1.b,test.t2.id |     1 | Using index              |
 
|    1 | PRIMARY     | t2          | ref    | PRIMARY,name  | name    | 257     | const                |     1 | Using where; Using index |
 
|    1 | PRIMARY     | t3          | eq_ref | PRIMARY,c     | PRIMARY | 8       | test.t1.b,test.t2.id |     1 | Using index              |
 
|    1 | PRIMARY     | t2          | ref    | PRIMARY,name  | name    | 257     | const                |     1 | Using where; Using index |
 
|    1 | PRIMARY     | t3          | eq_ref | PRIMARY,c     | PRIMARY | 8       | test.t1.b,test.t2.id |     1 | Using index              |
 
|    1 | PRIMARY     | t2          | ref    | PRIMARY,name  | name    | 257     | const                |     1 | Using where; Using index |
 
|    1 | PRIMARY     | t3          | eq_ref | PRIMARY,c     | PRIMARY | 8       | test.t1.b,test.t2.id |     1 | Using index              |
 
|    1 | PRIMARY     | t2          | ref    | PRIMARY,name  | name    | 257     | const                |     1 | Using where; Using index |
 
|    1 | PRIMARY     | t3          | eq_ref | PRIMARY,c     | PRIMARY | 8       | test.t1.b,test.t2.id |     1 | Using index              |
 
|    1 | PRIMARY     | t2          | ref    | PRIMARY,name  | name    | 257     | const                |     1 | Using where; Using index |
 
|    1 | PRIMARY     | t3          | eq_ref | PRIMARY,c     | PRIMARY | 8       | test.t1.b,test.t2.id |     1 | Using index              |
 
|    1 | PRIMARY     | t2          | ref    | PRIMARY,name  | name    | 257     | const                |     1 | Using where; Using index |
 
|    1 | PRIMARY     | t3          | eq_ref | PRIMARY,c     | PRIMARY | 8       | test.t1.b,test.t2.id |     1 | Using index              |
 
|    1 | PRIMARY     | t2          | ref    | PRIMARY,name  | name    | 257     | const                |     1 | Using where; Using index |
 
|    1 | PRIMARY     | t3          | eq_ref | PRIMARY,c     | PRIMARY | 8       | test.t1.b,test.t2.id |     1 | Using index              |
 
|    1 | PRIMARY     | t2          | ref    | PRIMARY,name  | name    | 257     | const                |     1 | Using where; Using index |
 
|    1 | PRIMARY     | t3          | eq_ref | PRIMARY,c     | PRIMARY | 8       | test.t1.b,test.t2.id |     1 | Using index              |
 
|    1 | PRIMARY     | t2          | ref    | PRIMARY,name  | name    | 257     | const                |     1 | Using where; Using index |
 
|    1 | PRIMARY     | t3          | eq_ref | PRIMARY,c     | PRIMARY | 8       | test.t1.b,test.t2.id |     1 | Using index              |
 
|    1 | PRIMARY     | t2          | ref    | PRIMARY,name  | name    | 257     | const                |     1 | Using where; Using index |
 
|    1 | PRIMARY     | t3          | eq_ref | PRIMARY,c     | PRIMARY | 8       | test.t1.b,test.t2.id |     1 | Using index              |
 
|    1 | PRIMARY     | t2          | ref    | PRIMARY,name  | name    | 257     | const                |     1 | Using where; Using index |
 
|    1 | PRIMARY     | t3          | eq_ref | PRIMARY,c     | PRIMARY | 8       | test.t1.b,test.t2.id |     1 | Using index              |
 
|    1 | PRIMARY     | t2          | ref    | PRIMARY,name  | name    | 257     | const                |     1 | Using where; Using index |
 
|    1 | PRIMARY     | t3          | eq_ref | PRIMARY,c     | PRIMARY | 8       | test.t1.b,test.t2.id |     1 | Using index              |
 
|    1 | PRIMARY     | t2          | ref    | PRIMARY,name  | name    | 257     | const                |     1 | Using where; Using index |
 
|    1 | PRIMARY     | t3          | eq_ref | PRIMARY,c     | PRIMARY | 8       | test.t1.b,test.t2.id |     1 | Using index              |
 
|    1 | PRIMARY     | t2          | ref    | PRIMARY,name  | name    | 257     | const                |     1 | Using where; Using index |
 
|    1 | PRIMARY     | t3          | eq_ref | PRIMARY,c     | PRIMARY | 8       | test.t1.b,test.t2.id |     1 | Using index              |
 
|    1 | PRIMARY     | t2          | ref    | PRIMARY,name  | name    | 257     | const                |     1 | Using where; Using index |
 
|    1 | PRIMARY     | t3          | eq_ref | PRIMARY,c     | PRIMARY | 8       | test.t1.b,test.t2.id |     1 | Using index              |
 
|    1 | PRIMARY     | <derived30> | ALL    | NULL          | NULL    | NULL    | NULL                 | 19731 | Using where              |
 
|    1 | PRIMARY     | <derived31> | ALL    | NULL          | NULL    | NULL    | NULL                 | 19731 | Using where              |
 
|   31 | SUBQUERY    | t3          | index  | NULL          | a       | 258     | NULL                 | 19731 | Using index              |
 
|   31 | SUBQUERY    | t2          | eq_ref | PRIMARY       | PRIMARY | 4       | test.t3.c            |     1 |                          |
 
|   30 | SUBQUERY    | t3          | index  | NULL          | a       | 258     | NULL                 | 19731 | Using index              |
 
|   30 | SUBQUERY    | t2          | eq_ref | PRIMARY       | PRIMARY | 4       | test.t3.c            |     1 |                          |
 
+------+-------------+-------------+--------+---------------+---------+---------+----------------------+-------+--------------------------+
 
64 rows in set (0.003 sec)

Comment by Igor Babaev [ 2019-06-04 ]

SuperC,
Could you please put it in plain words what is your problems is?
The fact is that currently the optimizer cannot handle more than 62 join tables when searching for the best execution plan. So some view instances cannot be merged, but rather are materialized.
I see that your query has a specific structure and could be executed in a better way if to lift the above limitation. Yet it would require a serious development not only for the optimizer part, but also for the execution part.

For the query you reported using one materialization of all instances of the same view that were not merged would help. And this is in our plans.
(see https://jira.mariadb.org/browse/MDEV-11050 : Reuse temporary tables created for materialized CTE tables)

Comment by SuperC [ 2019-06-05 ]

Thanks for your interest!

This queries operate on a few tables containing some million records; the conditions however are always very selective, so the queries will extract few records.
The 30-join query gets executed in a few milliseconds; when adding a join on the same view with similar (very selective) conditions, the new plan causes the server writing gigabytes of temporary files and high cpu usage for several minutes.
Sometimes the server crashed for no spece left on disk.
Maybe the chosen plan is writing all the big tables content (million records) in temporary tables, when in reality only some of them are needed.

Problems on the queries are:

  • they are variable, because they depend on user design, though always in the same logic
  • they can't be easily re-written, because are obtained through a framework; the only other option is completely re-thinking the persistence layer
Generated at Thu Feb 08 08:53:21 UTC 2024 using Jira 8.20.16#820016-sha1:9d11dbea5f4be3d4cc21f03a88dd11d8c8687422.