|
Running the following query in Maria 10.5 produces a table scan on table "h":
explain SELECT *
|
FROM
|
rdMPGGeraet g JOIN
|
rdMPGBezeichnung b ON g.BezeichnungID=b.ID JOIN
|
rdMPGAdresse h ON (b.Hersteller=h.ID ) LEFT JOIN
|
rdFunkruf rf ON rf.ID=(IF(g.Funkruf=0,g.StammFunkrufID,g.Funkruf) ) AND rf.OrganisationID IN(42) LEFT JOIN
|
rdFahrzeug f ON (f.ID=COALESCE(rf.Fahrzeug,rf.Stammfahrzeug,g.FesteinbauFID) AND f.OrganisationID IN(42)) LEFT JOIN
|
rdWache w ON (w.ID=COALESCE(rf.Wache,f.Wache,g.werkstatt) AND w.OrganisationID IN(42))
|
|
WHERE
|
g.OrganisationID IN(42) AND
|
g.Status="in Betrieb" AND
|
ISNULL(g.del)
|
id select_type table type possible_keys key key_len ref rows Extra
|
1 SIMPLE g ref einweisungPfl,BezeichnungID,ClusterID,Status,del Status 7 const,const,const 1157 Using index condition
|
1 SIMPLE rf eq_ref PRIMARY,Wache PRIMARY 2 func 1 Using where
|
1 SIMPLE f eq_ref PRIMARY,Status PRIMARY 2 func 1 Using where
|
1 SIMPLE w eq_ref PRIMARY,Ressource,Shop,Strassensperrung PRIMARY 2 func 1 Using where
|
1 SIMPLE h ALL PRIMARY NULL NULL NULL 5180 Using join buffer (flat, BNL join)
|
1 SIMPLE b ref PRIMARY,Hersteller Hersteller 3 qmsystems.h.ID 2 Using where
|
Same query in Maria 10.3.27
+------+-------------+-------+--------+--------------------------------------------------+---------+---------+---------------------------+------+-----------------------+
|
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
|
+------+-------------+-------+--------+--------------------------------------------------+---------+---------+---------------------------+------+-----------------------+
|
| 1 | SIMPLE | g | ref | einweisungPfl,BezeichnungID,ClusterID,Status,del | Status | 7 | const,const,const | 1157 | Using index condition |
|
| 1 | SIMPLE | rf | eq_ref | PRIMARY,Wache | PRIMARY | 2 | func | 1 | Using where |
|
| 1 | SIMPLE | f | eq_ref | PRIMARY,Status | PRIMARY | 2 | func | 1 | Using where |
|
| 1 | SIMPLE | w | eq_ref | PRIMARY,Ressource,Shop,Strassensperrung | PRIMARY | 2 | func | 1 | Using where |
|
| 1 | SIMPLE | b | eq_ref | PRIMARY,Hersteller | PRIMARY | 3 | qmsystems.g.BezeichnungID | 1 | |
|
| 1 | SIMPLE | h | eq_ref | PRIMARY | PRIMARY | 3 | qmsystems.b.Hersteller | 1 | |
|
+------+-------------+-------+--------+--------------------------------------------------+---------+---------+---------------------------+------+-----------------------+
|
removing AND *.OrganisationID IN(42) from joins gives the same result as MariaDB 10.3.
All columns have an index and *.OrganisationID IN(42) is added for security.
I have no clue how to reduce this to a smaller example. At least not using h primary key and doing a table scan is total nonsense.
|