MySQL Forums
Forum List  »  Optimizer & Parser

Re: Make optimized LEFT JOIN with GROUP BY
Posted by: Beat Vontobel
Date: May 03, 2006 12:58PM

Oh man! ;-) That's a little bit more complicated. I see multiple possible problems and would probably need the SHOW CREATE TABLE for both involved tables to really help here (plus your version of MySQL, there have been some recent additions to the optimizer in 5.0 that should help with such queries). I have maybe just not enough time (and experience) to really dig into this - but please still go ahead and post this info, maybe somebody else can help.

The problems here are at first sight:
a) You have many additional WHERE restrictions (that you probably all want to make use of an INDEX)
b) You ORDER BY a column you didn't GROUP BY

This could make it a little bit tricky to come up with a solution that can really make best use of a multi-column index here. Just remember that in a multi-column index only columns of the front of the index can be used: KEY(a,b,c) can be used as (a), (a,b), (a,b,c), but as nothing else.

A possible guideline would be:

a) Add the column you ORDER BY also to the GROUP BY clause (if this is possible, which seems to be the case here, as ID and Datum both come from table recepti, where ID seems to be the PRIMARY KEY, so this won't change the semantics). This could maybe help the optimizer to also use the index for the ORDER BY (if you ORDER BY a column that wasn't GROUPed BY, the optimizer will probably have to build a derived table first and sort that in the end without using any indexes).
b) Build the multi-column indexes on the tables to have the columns used in the JOIN first, followed by the columns that are used in equality comparisons against constants and the GROUP BY and finally ORDER BY columns.
c) Then play around with different orders of the multi-column indexes, always check EXPLAIN and also measure execution time (EXPLAIN sometimes lies a little bit). EXPLAIN EXTENDED followed by SHOW WARNINGS gives you the rewritten query that's actually used by the optimizer.

Good luck! ;-)

Beat Vontobel

Options: ReplyQuote

Written By
Re: Make optimized LEFT JOIN with GROUP BY
May 03, 2006 12:58PM

Sorry, you can't reply to this topic. It has been closed.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.