mysql - 有索引，返回不同的结果

Question

以前从来没有见过这个。运行相同的查询，1 强制索引。没有索引，结果不正确(顺序错误)，有索引，结果顺序正确。使用索引的唯一问题是它由于某种原因变慢了。索引在 comment_id 和 user_id 上

没有索引:

SELECT DISTINCT topic_id FROM comments
WHERE user_id=9384
AND (status = 1 or status = 0)
ORDER BY comment_id DESC LIMIT 15

带索引:

SELECT DISTINCT topic_id FROM comments force index(index_comment_user)
WHERE user_id=9384
AND (status = 1 or status = 0)
ORDER BY comment_id DESC LIMIT 15

有什么想法吗？我真的很想在不减慢查询速度的情况下获得正确的顺序。我会通过索引来做到这一点。

这是 SQL 结构。

CREATE TABLE  `db`.`comments` (
  `comment_id` int(10) unsigned NOT NULL auto_increment,
  `old_comments_id` mediumint(8) unsigned default NULL,
  `user_id` mediumint(8) unsigned default NULL,
  `content` text character set latin1,
  `status` tinyint(3) unsigned default NULL,
  `added_date` datetime default NULL,
  `category_id` tinyint(3) unsigned default NULL,
  `helpful` tinyint(3) unsigned default NULL,
  `modified_date` datetime default NULL,
  `topic_id` mediumint(8) unsigned default NULL,
  `last_mod_user_id` mediumint(8) unsigned default NULL,
  PRIMARY KEY  USING BTREE (`comment_id`),
  KEY `Index_user_id` (`user_id`),
  KEY `Index_added_date` (`added_date`),
  KEY `Index_comments_status` USING BTREE (`status`),
  KEY `Index_user_activity` USING BTREE (`comment_id`,`user_id`),
  KEY `Index_user_activity2` USING BTREE (`user_id`,`topic_id`),
  KEY `Index_question_id` USING BTREE (`topic_id`,`status`),
  KEY `Index_user_activity3` (`user_id`,`status`,`topic_id`,`comment_id`)
) ENGINE=InnoDB AUTO_INCREMENT=2040237 DEFAULT CHARSET=utf8;

Answer 1

在您未选择的列上同时使用 DISTINCT 和 ORDER BY 会给您带来问题。尝试改用 GROUP BY:

SELECT topic_id, MAX(comment_id) AS comment_id
FROM comments
WHERE user_id=9384 AND status IN (0, 1)
GROUP BY topic_id
ORDER BY comment_id DESC
LIMIT 15

您不需要强制索引。只需添加正确的索引，它就会自动使用。您可能想尝试对索引中的列进行不同的组合和排序，看看哪种效果最好。