Slow query

Submitted by jakob on Mon, 03/14/2011 - 18:17

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 :-)

Tags: