id,summary,reporter,owner,description,type,status,priority,milestone,component,version,severity,resolution,keywords,cc
7415,"""using filesort"" in default install",dbuser123,anonymous,"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;
}

",defect (bug),closed,high,2.8,Optimization,2.7,major,fixed,,djr aaroncampbell simon@…