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.

Answer 2

The manual says

If the table has a multiple-column index, any leftmost prefix of the index can be used by the optimizer to find rows. For example, if you have a three-column index on (col1, col2, col3), you have indexed search capabilities on (col1), (col1, col2), and (col1, col2, col3).

i.e. I think you need an index on user_id, column_id for your query - it doesn't sound like mysql can use column_id, user_id. You should run EXPLAIN SELECT on both of the queries to verify this.

Why it goes wrong with the other index I don't know sorry.

Answer 3

Please provide the result of

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

I'd like to know if it matters if the alias for MAX(comment_id) is the same name with a column or not. It is ambiguous, how can you tell if mysql is using the column, or the aggregate function for sort?

Answer 4

This is the common "max per group" problem, and usually it is solved like this:

SELECT
  comment.user_id, 
  comment.topic_id,
  comment.comment_id
FROM
  comment
  INNER JOIN (
      SELECT user_id, topic_id, MAX(comment_id) AS comment_id
        FROM comments 
    GROUP BY user_id, topic_id
  ) AS max ON max.comment_id = comment.comment_id
WHERE
  comment.user_id = 9384
  AND comment.status IN (1, 0)
ORDER BY
  comment.comment_id DESC