Details
-
Bug
-
Status: Closed (View Workflow)
-
Critical
-
Resolution: Incomplete
-
10.5.15
-
None
Description
I have several queries where the optimizer reoders the tables which result in massive table scans.
I have several tables (all with indices) which are full joined as 1:1, but the optimizer prefers to sort it by a left joins and therefore can't use the indices given. I tried to reproduce this on simple tables, but I was not successfull. See the following query and the query plan:
SELECT COUNT(*) OVER() AS cnt FROM rdMPGGeraet g JOIN |
rdMPGBezeichnung b ON (g.BezeichnungID=b.ID) JOIN |
rdMPGGeraeteTyp t ON (b.GeraeteTyp=t.ID) JOIN |
rdMPGAdresse h ON (b.Hersteller=h.ID) LEFT JOIN |
rdFunkruf rf ON (rf.OrganisationID IN(103) AND rf.ID=(IF(g.Funkruf=0,g.StammFunkrufID,g.Funkruf))) LEFT JOIN |
rdFahrzeug f ON (f.OrganisationID IN(103) AND f.ID=COALESCE(rf.Fahrzeug,rf.Stammfahrzeug,g.FesteinbauFID)) LEFT JOIN |
rdWache w ON (w.OrganisationID IN(103) AND w.ID=COALESCE(rf.Wache,f.Wache,g.werkstatt)) LEFT JOIN |
qmBereich br ON (br.OrganisationID IN(103) AND br.ID=w.HauptbereichID) LEFT JOIN |
rdWache werk ON (werk.OrganisationID IN(103) AND werk.ID=g.werkstatt) LEFT JOIN |
rdMPGClusterValues c ON (c.OrganisationID IN(103) AND g.ClusterID=c.ID) WHERE g.OrganisationID IN(103) AND (w.ID=1119 OR g.werkstatt=1119) AND b.Einweisung="unterweisungspflichtig" AND ISNULL(g.del) LIMIT 1 |
The query plan is as folows:
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
| 1 | SIMPLE | g | ref | einweisungPfl,BezeichnungID,werkstatt,ClusterID,del | ClusterID | 2 | const | 656 | Using where; Using temporary |
| 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 | t | index | PRIMARY | PRIMARY | 3 | NULL | 2457 | Using index; Using join buffer (flat, BNL join) |
| 1 | SIMPLE | b | ref | PRIMARY,Hersteller,GeraeteTyp | GeraeteTyp | 3 | qmsystems.t.ID | 2 | Using where |
| 1 | SIMPLE | h | eq_ref | PRIMARY | PRIMARY | 3 | qmsystems.b.Hersteller | 1 | Using index |