[wp-trac] Re: [WordPress Trac] #7415: "using filesort" in default install

[WordPress Trac]

#7415: "using filesort" in default install
------------------------------------------------------------+---------------
 Reporter:  dbuser123                                       |        Owner:  anonymous
     Type:  defect (bug)                                    |       Status:  new      
 Priority:  high                                            |    Milestone:  2.8      
Component:  Optimization                                    |      Version:  2.7      
 Severity:  major                                           |   Resolution:           
 Keywords:  database, optimization, slow queries, filesort  |  
------------------------------------------------------------+---------------
Comment (by rawalex):

 as there are a limited number of queries against the wp-posts file, it
 isn't unreasonable to have an index for each of the major combinations.
 So having an index on post type, post status, post date isn't a bad idea,
 example.

 That however is really a nit pick, the true problematic queries use
 SQL_CALC_FOUND_ROWS in them.  MySQL cannot use any index for these
 queries, and as a reuslt, they are non-scalable queries.  They are a neat
 sort of code when you are playing with a small database, but get to 10,000
 records and they turn into pigs.  Doing a standard select followed by a
 simple mysql_num_rows is hugely more effecient.  it is potentially a
 "lock" query that also won't allow other simultanious queries, which makes
 it even worse.

 I would suggest a developer load up a single blog install with 10k posts
 spread over 2 years, and then use a bot to request each of the archive
 pages (1 a second or so).  By the 5th or 6th request, your server
 performance will tank.  I have had googlebot all but wipe out a strong
 dual core server asking for archive pages.

-- 
Ticket URL: <http://trac.wordpress.org/ticket/7415#comment:13>
WordPress Trac <http://trac.wordpress.org/>
WordPress blogging software