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

[WordPress Trac]

#7415: "using filesort" in default install
--------------------------+-------------------------------------------------
 Reporter:  dbuser123     |       Owner:  anonymous                                     
     Type:  defect        |      Status:  new                                           
 Priority:  normal        |   Milestone:  2.7                                           
Component:  Optimization  |     Version:                                                
 Severity:  normal        |    Keywords:  database, optimization, slow queries, filesort
--------------------------+-------------------------------------------------
 I took the database of a large existing WP blog (10k posts, 30k comments)
 and got it to work with a fresh and clean WP install with the Default
 theme without any plugins.

 There are ridiculously many queries executed: 50. Here are some
 recommendations:

 Almost half of them are like "SELECT `post_parent` FROM wp_posts WHERE ID=
 '...' LIMIT 1", just after a "SELECT * FROM wp_posts WHERE (post_type =
 'page' AND post_status = 'publish') ORDER BY menu_order, post_title ASC".
 A left join would be appropriate. Even if you don't want to join, you
 could cache the post_parent because many posts could have the same parent.


 The slowest query by far (40% of total query time) is this one:
 SELECT SQL_CALC_FOUND_ROWS wp_posts.* FROM wp_posts WHERE 1=1 AND
 wp_posts.post_type = 'post' AND (wp_posts.post_status = 'publish' OR
 wp_posts.post_status = 'private') ORDER BY wp_posts.post_date DESC LIMIT
 0, 6
 This query had to sort 3000 rows BY HAND (only if logged in as admin).
 Because of the OR, the post_date part of the index
 (post_type,post_status,post_date,ID) could not be used for sorting. A
 UNION would solve this problem:
 (SELECT wp_posts.* FROM wp_posts WHERE wp_posts.post_type = 'post' AND
 wp_posts.post_status = 'publish' ORDER BY wp_posts.post_date DESC LIMIT 0,
 6)
 UNION
 (SELECT wp_posts.* FROM wp_posts WHERE wp_posts.post_type = 'post' AND
 wp_posts.post_status = 'private' ORDER BY wp_posts.post_date DESC LIMIT 0,
 6)
 ORDER BY post_date DESC LIMIT 0,6

 The second problem with this query is the SQL_CALC_FOUND_ROWS. It makes
 MySQL retrieve all rows, thus causing much disk i/o. It's way faster to do
 a second query that does a count(*) because it can be done by reading the
 index instead of the rows. This made the query 10 times as fast for me.


 Next one: SELECT post_id, meta_key, meta_value FROM wp_postmeta WHERE
 post_id IN (9109,9103,9052,9112,9100,9096) ORDER BY post_id, meta_key
 I have no idea why it has to be sorted by meta_key as well, but as the
 table is not indexed on (post_id,meta_key), it causes a manual sort.


 Yet another one: SELECT * FROM wp_posts WHERE (post_type = 'page' AND
 post_status = 'publish') ORDER BY menu_order, post_title ASC
 Has to be ordered manually again because there's no appropriate index.


 I hope somebody will look at the db-optimization because there's still
 lots of room for improvement. To assist you, set SAVEQUERIES to true and
 change the destructor of wp-db.php:


 function __destruct() {
         $ttime = 0;
         foreach($this->queries as $q) $ttime += $q[1];
         echo '<div style="text-align:left; font-family:courier new; font-
 size: 14;">';
         foreach($this->queries as $q) {
                 $i++;
                 echo '[' . $i . '] (' . round(100*$q[1]/$ttime,5) . '% - '
 . round($q[1],5) . 's) ' . htmlspecialchars($q[0]) . '<br />';
         }
         echo '</div>';
         return true;
 }

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