with index, returns different result

Question

Never seen this before. Running the same query, 1 forces an index. Without the index, the results are incorrect (in wrong order), with the index the results are in the correct order. Only problem with using the index is that its slower for some reason. Index is on comment_id and user_id

without index:

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

with index:

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

Any ideas? I really want to get the correct order without slowing the query down. I would have throught an index would have done that.

Here is the SQL structure.

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

Your use of DISTINCT together with an ORDER BY on a column you are not selecting will give you problems. Try using a GROUP BY instead:

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

You shouldn't need to force the index. Just add the correct index and it should be used automatically. You might want to try different combinations and ordering of the columns in the index to see which works best.