StackOverflow2013

Note that there are some explanatory texts on larger screens.

plurals

POwith index, returns different result
text
Body
copied!<p>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</p> <p>without index:</p> <pre><code>SELECT DISTINCT topic_id FROM comments WHERE user_id=9384 AND (status = 1 or status = 0) ORDER BY comment_id DESC LIMIT 15 </code></pre> <p>with index:</p> <pre><code>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 </code></pre> <p>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.</p> <p>Here is the SQL structure.</p> <pre><code>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; </code></pre>

Querying!

Guidance

An individual column

Larger individual text columns get their own page to allow for proper reading.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload