|
In the optimizer trace from the related MDEV-30877 we can see that the following query:
MariaDB [(none)]> analyze
|
-> SELECT
|
-> esd.esd_c1,
|
-> esd.esd_c2,
|
-> esd.esd_c3,
|
-> es.es_c1,
|
-> es.es_c2,
|
-> es.es_c3,
|
-> es.es_c4,
|
-> es.es_c5,
|
-> es.es_c6,
|
-> es.es_c7,
|
-> es.es_c8
|
-> FROM
|
-> DB1.t1 esd
|
-> INNER JOIN
|
-> DB1.t2 es ON esd.t1Id = es.t1Id
|
-> INNER JOIN
|
-> (SELECT
|
-> esd.esd_c1, MAX(es.es_c1) AS last_set
|
-> FROM
|
-> DB1.t1 esd
|
-> INNER JOIN DB1.t2 es ON esd.t1Id = es.t1Id
|
-> GROUP BY esd.esd_c1) q ON esd.esd_c1 = q.esd_c1
|
-> WHERE
|
-> es.es_c1 = q.last_set;
|
+------+-----------------+------------+-------+---------------------------+-----------+---------+----------------------------------------------+-------+----------+----------+------------+--------------------------+
|
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | r_rows | filtered | r_filtered | Extra |
|
+------+-----------------+------------+-------+---------------------------+-----------+---------+----------------------------------------------+-------+----------+----------+------------+--------------------------+
|
| 1 | PRIMARY | esd | index | PRIMARY,UNIQUEMSG,Index_3 | UNIQUEMSG | 137 | NULL | 15197 | 15197.00 | 100.00 | 100.00 | Using where; Using index |
|
| 1 | PRIMARY | <derived2> | ref | key0 | key0 | 33 | DB1.esd.esd_c1 | 2 | 0.98 | 100.00 | 100.00 | Using where |
|
| 1 | PRIMARY | es | ref | PRIMARY | PRIMARY | 9 | DB1.esd.t1Id,q.last_set | 157 | 21.26 | 100.00 | 100.00 | |
|
| 2 | LATERAL DERIVED | esd | ref | PRIMARY,UNIQUEMSG,Index_3 | UNIQUEMSG | 33 | DB1.esd.esd_c1 | 7 | 17.40 | 100.00 | 100.00 | Using index |
|
| 2 | LATERAL DERIVED | es | ref | PRIMARY | PRIMARY | 4 | DB1.esd.t1Id | 875 | 783.76 | 100.00 | 100.00 | Using index |
|
+------+-----------------+------------+-------+---------------------------+-----------+---------+----------------------------------------------+-------+----------+----------+------------+--------------------------+
|
5 rows in set (1 min 12.157 sec)
|
We see many entries like these:
...
|
"steps": [
|
{
|
"join_execution": {
|
"select_id": 2,
|
"steps": []
|
}
|
},
|
{
|
"join_execution": {
|
"select_id": 2,
|
"steps": []
|
}
|
},
|
{
|
"join_execution": {
|
"select_id": 2,
|
"steps": []
|
}
|
},
|
{
|
"join_execution": {
|
"select_id": 2,
|
"steps": []
|
}
|
},
|
...
|
that do not make much sense, do not add much value but lead to a huge optimizer trace. I think such output is a bug and should be changed to something more useful.
|
optimizer_trace_mangled.txt