List:General Discussion« Previous MessageNext Message »
From:Michael Widenius Date:May 11 1999 8:17am
Subject:How to optimize this query for MySQL?
View as plain text  
Hi!

>>>>> "Matthias" == Matthias Pigulla <mp@stripped> writes:

<cut>

Matthias> SELECT

Matthias>
> threads.id,threads.topic,users.nick,COUNT(threads.id)-1,MAX(postings.post_date)
Matthias> as lastpost
Matthias> FROM
Matthias>  threads,postings,users
Matthias> WHERE
Matthias>  threads.forum=(any valid id)
Matthias>  AND postings.thread=threads.id
Matthias>  AND users.id=postings.user
Matthias> GROUP BY threads.id
Matthias> ORDER BY lastpost DESC

Matthias> In my naive idea of "database internals" I expected MySQL to
Matthias> - fetch all threads for the group/forum
Matthias> - for each thread, quickly retrieve MAX(post_date) with the compound
Matthias> index described above
Matthias> - add the author

<cut>

Sorry;  MySQL can't yet optimize MAX() on a join between many tables.
(It can only optimize MAX() on a compound index on select involving one table)
When one takes this into consideration, the EXPLAIN isn't that bad.

You can force MySQL to use threads as your first table by using
STRAIGHT_JOIN, but as I don't know the distribution of your keys, I
don't know if this will be any better.

<cut>

Matthias> Now I wonder what to do. Is there a flaw in my database design? Is it an
Matthias> unwise decision to have COUNT, MAX and GROUP BY in my query - or more in
Matthias> general, are these functions indicating design flaws like NULL values
Matthias> are?

Another possibility would be to have a summary table with the MAX()
information which you update at the same times as the postings table. This
will make the above query MUCH faster.

Regards,
Monty
Thread
How to optimize this query for MySQL?Matthias Pigulla7 May
  • Re: How to optimize this query for MySQL?Christian Mack7 May
  • How to optimize this query for MySQL?Michael Widenius11 May
Re: MyODBCEIN)28 Mar