mysql - 具有多个 where 和 order by 子句的慢查询

Question

我正在尝试寻找一种方法来加速缓慢的(文件排序)MySQL 查询。

表格:

categories (id, lft, rgt)
questions (id, category_id, created_at, votes_up, votes_down)

示例查询:

SELECT * FROM questions q 
INNER JOIN categories c ON (c.id = q.category_id)
WHERE c.lft > 1 AND c.rgt < 100
ORDER BY q.created_at DESC, q.votes_up DESC, q.votes_down ASC
LIMIT 4000, 20

如果我删除 ORDER BY 子句，它会很快。我知道 MySQL 不喜欢在同一个子句中同时使用 DESC 和 ASC 命令，所以我尝试添加一个复合 (created_at, votes_up) 索引到 questions 表并从 ORDER BY 子句中删除 q.votes_down ASC。这没有帮助，而且 WHERE 子句似乎妨碍了这里，因为它按来自另一个 (categories) 表的列进行过滤。然而，即使它有效，它也不是很正确，因为我确实需要 q.votes_down ASC 条件。

在这种情况下，提高性能的好策略是什么？如果可能的话，我宁愿避免重组表格。

编辑:

CREATE TABLE `categories` (
  `id` int(11) NOT NULL auto_increment,
  `lft` int(11) NOT NULL,
  `rgt` int(11) NOT NULL,
  PRIMARY KEY  (`id`),
  KEY `lft_idx` (`lft`),
  KEY `rgt_idx` (`rgt`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;

CREATE TABLE `questions` (
  `id` int(11) NOT NULL auto_increment,
  `category_id` int(11) NOT NULL,
  `votes_up` int(11) NOT NULL default '0',
  `votes_down` int(11) NOT NULL default '0',
  `created_at` datetime NOT NULL,
  PRIMARY KEY  (`id`),
  KEY `questions_FI_1` (`category_id`),
  KEY `votes_up_idx` (`votes_up`),
  KEY `votes_down_idx` (`votes_down`),
  KEY `created_at_idx` (`created_at`),
  CONSTRAINT `questions_FK_1` FOREIGN KEY (`category_id`) REFERENCES `categories` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;

id select_type table type   possible_keys           key     key_len ref                 rows  Extra
1  SIMPLE      q     ALL    questions_FI_1          NULL    NULL    NULL                31774 Using filesort
1  SIMPLE      c     eq_ref PRIMARY,lft_idx,rgt_idx PRIMARY 4       ttt.q.category_id 1       Using where

Answer 1

尝试使用子查询来获取所需的类别:

SELECT * FROM questions 
WHERE category_id IN ( SELECT id FROM categories WHERE lft > 1 AND rgt < 100 )
ORDER BY created_at DESC, votes_up DESC, votes_down ASC
LIMIT 4000, 20