代码之家 › 专栏 › 技术社区 › Edward Dale

为旧日志列表优化WordPress SQL查询

query-optimization wordpress mysql sql

Edward Dale · 技术社区 · 15 年前

在我的WordPress网站上,当一个用户的页面远远落后于帖子列表时,查询会花费几秒钟的时间。我想把这个拿下来。下面是正在执行的查询:

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

表中大约有160K行。下面是模式的简化版本:

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

这就是 EXPLAIN 查询结果:

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

理想情况下,我想去掉文件排序。有什么小窍门吗?

5 回复 | 直到 14 年前

windyjonas 15 年前

您必须在排序列(post_date)上创建一个索引,如果没有索引,所有160k行都将被提取、文件排序,然后大部分将被丢弃。

您可能还希望看到更积极的缓存,将结果窗口存储在memcache或类似的缓存引擎中。

有关优化分页显示的更多提示,请访问 http://www.mysqlperformanceblog.com/2008/09/24/four-ways-to-optimize-paginated-displays/

Peter Lang 15 年前

确保在上有索引 post_type , post_status 和 post_date .

160K行应该不是问题。

raveren 15 年前

使用时 OR ,确保在其两侧使用索引。

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

我也会做 post_status 和 post_type 永远是。同时在排序列上添加单独的索引( post_date )和多索引一样,只有左边的第一列可以用作单个索引。

-1

LukáÅ¡ Lalinský 15 年前

好吧,这只是一个猜测,但无论如何:我会尽力 post_date 首先在多列索引中。然后根据日期对索引进行排序,并通过遍历索引对结果进行排序。不过,我没有尝试过它是否真的在MySQL中有效。

-1

Tom H zenazn 15 年前

看看 http://www.slideshare.net/Eweaver/efficient-pagination-using-mysql . 那里的建议基本上归结为

有一个合适的索引,可以在和 ORDER BY子句。
避免使用 COUNT() (或其表妹) SQL_CALC_FOUND ROWS )用于显示行(幻灯片根据需要在单独字段中列出几个UI更改或聚合)。
不要使用 LIMIT M, N 使用 LIMIT N (实现这一点的方法相当聪明:例如,如果你把帖子按主键 DESC ,然后您可以通过添加主键小于当前页的最后一个元素)。

编辑: 第一点在Peter Lang的回答中介绍,但是请注意,如果传呼机允许对其他列进行排序,那么索引可能不够。