Optimizing WordPress SQL query for older posts list

Question

On my WordPress site, when a user pages far back in the list of posts, the queries end up taking a couple seconds. I'd like to bring this down. Here's the query that's being executed:

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 846, 47

There are about 160k rows in the table. Here's a simplified version of the schema:

CREATE TABLE `wp_posts` (
  `ID` bigint(20) unsigned NOT NULL auto_increment,
  `post_date` datetime NOT NULL default '0000-00-00 00:00:00',
  `post_status` varchar(20) NOT NULL default 'publish',
  `post_type` varchar(20) NOT NULL default 'post',
  PRIMARY KEY  (`ID`),
  KEY `type_status_date` (`post_type`,`post_status`,`post_date`,`ID`),
) ENGINE=MyISAM DEFAULT CHARSET=utf8

This is the EXPLAIN result of the query:

id  select_type table   type    possible_keys   key key_len ref rows    Extra   
1   SIMPLE  wp_posts    ref type_status_date        type_status_date        62  const   41519   Using where; Using filesort

Ideally, I'd like to get rid of the filesort. Any tips?

Answer 1

Make sure to have an index on post_type, post_status and post_date.

160k rows should not be a problem then.

Answer 2

Well, this is just a guess, but anyway: I'd try to put post_date in the multi-column index first. Then the index should be sorted by the date and the result sorting can be done by traversing the index. I've not tried if it really works in MySQL though.

Answer 3

When using OR, assure indexes are used on both sides of it.

SELECT SQL_CALC_FOUND_ROWS wp_posts.* # change this to select only columns you need
FROM wp_posts 
WHERE (wp_posts.post_type = 'post' AND wp_posts.post_status = 'publish') or (wp_posts.post_type = 'post' OR wp_posts.post_status = 'private') 
ORDER BY wp_posts.post_date DESC LIMIT 846, 47

I'd also make post_status and post_type be enum. Also add a separate index on the ordering column (post_date), as in multi indexes only the first column on the left can be used as a individual index.

Answer 4

Have look at http://www.slideshare.net/Eweaver/efficient-pagination-using-mysql. The recommendations there basically boil down to

• Have a suitable index that can be used both in the where and order by clause.
• Avoid usage of COUNT() (or its cousin SQL_CALC_FOUND ROWS) for displaying row (slides list a couple of UI changes or aggregation in separate field as necessary to achieve this).
• Do not use LIMIT M, N use LIMIT N (the way to achieve this is quite clever: If for instance you have the posts sorted by primary key DESC, then you can get the next page by adding a primary key less than the last element of your current page).

EDIT: The first point is covered in Peter Lang's answer, but please note, that if the pager allows sorting on other columns, then the index might not be sufficient.

Answer 5

You have to create an index on the ordering column (post_date), without the index all 160k rows will be fetched, filesorted and then most of them discarded.

You might also want to look at more aggressive caching, storing result-windows in memcache or similar cache engine.

More tips on optimizing paginated displays at http://www.mysqlperformanceblog.com/2008/09/24/four-ways-to-optimize-paginated-displays/

Answer 6

I'm having the same problem as the OP.

The query SELECT SQL_CALC_FOUND_ROWS is crashing my server a couple times per day.

How do I tell SQL to use an index? This query didn't work for me: USE INDEX (post_date_idx), even thou I created that index.

Is the default index type_status_date not sufficient?