MySQL Forums
Forum List  »  Optimizer & Parser

Re: avoid filesort
Posted by: Rick James
Date: January 25, 2012 08:26PM

How much RAM do you have? This is a large cache just for indexes:
> key_buffer_size 2415919104
For more discussion:
It may be eating into the space for _data_ caching.

The EXPLAIN says that it is completely scanning one table, plus reaching for about 8 rows in the other table for each of the first table's rows.

This is a "forum", correct? And the SELECT is called more than the INSERTs into f_thread, correct? Or, at least, it is working a lot harder.

So, here is a "solution":

1. Add a last_post column to f_thread.
2. In the ALTER for step 1, also ADD INDEX(last_post, thread_subject). (If thread_subject is TEXT, then instead do simpley ADD INDEX(last_post).)
3. Change the code to UPDATE f_thread.last_post whenever a new post comes in. This will be only a small overhead.
4. Populate f_thread.last_post via an UPDATE somewhat like your current SELECT.
5. Change the SELECT to look only at f_thread. This will now run significantly faster.

Options: ReplyQuote

Written By
January 22, 2012 01:08PM
January 24, 2012 01:17AM
January 24, 2012 04:36PM
January 25, 2012 07:24PM
January 26, 2012 04:57AM
Re: avoid filesort
January 25, 2012 08:26PM
January 26, 2012 04:56AM

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.