>>>>> "Matthias" == Matthias Pigulla <mp@stripped> writes:
Matthias> as lastpost
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
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.
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
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.