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:
http://mysql.rjweb.org/doc.php/memory
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


Subject
Views
Written By
Posted
1620
January 22, 2012 01:08PM
1000
January 24, 2012 01:17AM
1092
January 24, 2012 04:36PM
1185
January 25, 2012 07:24PM
1026
January 26, 2012 04:57AM
Re: avoid filesort
1024
January 25, 2012 08:26PM
997
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.