This query took 47 sec. to fetch 45 rows:
SELECT DISTINCT sol_folk.id, sol_folk.navn FROM ( sol_folk, sol_bestillingsregnskab, sol_indbetalingsgruppe ) LEFT OUTER JOIN sol_varebestilling ON sol_varebestilling.bestilling = sol_bestillingsregnskab.bestilling AND sol_varebestilling.person = sol_folk.id LEFT OUTER JOIN sol_indbetaling ON sol_indbetaling.person = sol_folk.id AND sol_indbetaling.gruppe = sol_indbetalingsgruppe.id WHERE sol_bestillingsregnskab.regnskab = sol_indbetalingsgruppe.regnskab AND sol_indbetalingsgruppe.regnskab = 12 AND ( sol_indbetaling.penge IS NOT NULL OR sol_varebestilling.vare IS NOT NULL )
when the index on sol_varebestilling
was defined as PRIMARY ( vare, person, bestilling )
. After changing the indexing order to ( bestilling, person, vare )
, the same query took below 3 sec.; and using two separate queries, one for
( sol_folk, sol_indbetaling, sol_indbetalingsgruppe )
and one for
( sol_folk, sol_bestillingsregnskab, sol_varebestilling )
, with no outer joins, fetched me the same rows in less than 1/10 of a second.
Moral: Be clever with multiple-column indexes AND don't use left outer unless you have to :-)