Hi,
I have a question about query optimization.
There is this query, which is really slow:
SELECT firma_name_1, firma_name_2, pers_anrede, pers_titel, pers_vorname,
pers_nachname,
pers_titel_nach_name, pers_stand_position, adr_strasse, adr_land,
adr_plz, adr_ort, adr_strasse,
adr_land, adr_plz, adr_ort, kundepersonhc.pers_id,
kundefirmahc.firma_id, stand_id, pershc_titel_lang
FROM kundefirmahc
LEFT OUTER JOIN standorthc ON kundefirmahc.firma_id=stand_firma_id
LEFT OUTER JOIN adresse ON stand_adr_id=adr_id
LEFT OUTER JOIN personstandort ON standorthc.stand_id=pers_stand_stand_id
LEFT OUTER JOIN kundepersonhc ON pers_stand_pers_id=kundepersonhc.pers_id
WHERE ( UPPER(kundepersonhc.pers_nachname) LIKE UPPER('me%') )
LIMIT 20;
If I change it to tis (only the order of the tables are changed), it works
quite good:
SELECT firma_name_1, firma_name_2, pers_anrede, pers_titel, pers_vorname,
pers_nachname,
pers_titel_nach_name, pers_stand_position, adr_strasse, adr_land,
adr_plz, adr_ort, adr_strasse,
adr_land, adr_plz, adr_ort, kundepersonhc.pers_id,
kundefirmahc.firma_id, stand_id ,pershc_titel_lang
FROM kundepersonhc
LEFT OUTER JOIN personstandort ON kundepersonhc.pers_id=pers_stand_pers_id
LEFT OUTER JOIN standorthc ON pers_stand_stand_id=stand_id
LEFT OUTER JOIN kundefirmahc ON kundefirmahc.firma_id=stand_firma_id
LEFT OUTER JOIN adresse ON stand_adr_id=adr_id
WHERE ( UPPER(kundepersonhc.pers_nachname) LIKE UPPER('me%') )
LIMIT 20;
The WHERE clause is created dynamically and may contain fields of each
table which is part of the join, so I can't just change the order of the
tables in my code to get the best result. Is there a way to make those
optimizations (re-ordering of tables) automatically?
Regards,
Verena