[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         |    Keywords:  database, optimization, slow queries, filesort
--------------------------+-------------------------------------------------

Comment(by rawalex):

 Replying to [comment:33 mrmist]:
 > Replying to [comment:32 rawalex]:
 > > Because of the overhead of calc_found_rows, wouldn't it just be better
 to make the first query full without limits and count it, and control the
 number of items displayed elsewhere?  Right now you are doing two full
 queries, which would be more intensive, no?
 >
 > The mysql docs say
 >
 >
 > {{{
 > If you are using SELECT SQL_CALC_FOUND_ROWS, MySQL must calculate how
 many rows are in the full result set. However, this is faster than running
 the query again without LIMIT, because the result set need not be sent to
 the client.
 > }}}
 >
 >
 > And I'm tempted to agree. If you have a massive results set the last
 thing you would want to do is return the entire thing to the client.
  The point is run the query (normally without the "count" and then just do
 a mysql_num_rows() on the result. That means you only do the query once,
 and then ask it how many (which is a very low load request).  The limit is
 meaningless in the current query (because the entire database must be
 considered), so the limit would be just as well done in software as put
 here.

 Moving also to replace the year() and month() parts of the queries with
 actual start and end dates would also improve the query dramatically.

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