Comment 8 for bug 1405155

Verified on PS 5.5.40 with given table data.

nilnandan@desktop:~/sandboxes/msb_5_5_40$ mysql -uroot -p --socket=/tmp/mysql_sandbox5540.sock
Enter password:
ERROR 1045 (28000): Access denied for user 'root'@'localhost' (using password: YES)
nilnandan@desktop:~/sandboxes/msb_5_5_40$ mysql -uroot -p --socket=/tmp/mysql_sandbox5540.sock
Enter password:
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 2
Server version: 5.5.40-36.1-log Percona Server (GPL), Release 36.1, Revision 707

Copyright (c) 2009-2014 Percona LLC and/or its affiliates
Copyright (c) 2000, 2014, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql>
mysql> source /home/nilnandan/Downloads/goods_comments.sql
..
mysql> show create table goods_comments \G
*************************** 1. row ***************************
       Table: goods_comments
Create Table: CREATE TABLE `goods_comments` (
  `id` int(11) unsigned NOT NULL AUTO_INCREMENT COMMENT 'ID',
  `text` text NOT NULL COMMENT 'Текст',
  `created` datetime NOT NULL COMMENT 'Дата создания',
  `ip` int(11) unsigned NOT NULL COMMENT 'ip',
  `status` tinyint(3) unsigned NOT NULL COMMENT 'Статус',
  `record_id` mediumint(8) unsigned NOT NULL,
  `name` varchar(64) NOT NULL COMMENT 'Имя',
  `email` varchar(64) NOT NULL COMMENT 'Электронная почта',
  `user_id` mediumint(8) unsigned NOT NULL,
  `mark` tinyint(3) unsigned DEFAULT NULL COMMENT 'Оценка товара',
  `rating` smallint(5) NOT NULL COMMENT 'Рейтинг коментария',
  `count_voices` smallint(5) unsigned NOT NULL COMMENT 'Общее количество голосов коментария',
  `summary` varchar(255) NOT NULL COMMENT 'Краткое содержание',
  `dignity` text NOT NULL COMMENT 'Плюсы',
  `shortcomings` text NOT NULL COMMENT 'Минусы',
  `parent_comment_id` int(11) unsigned NOT NULL,
  `subscribed_on_replies` tinyint(3) unsigned NOT NULL COMMENT 'Подписан на ответы',
  `positive_vote_count` mediumint(8) unsigned NOT NULL COMMENT 'Количество положительных отзывов',
  `negative_vote_count` mediumint(8) unsigned NOT NULL COMMENT 'Количество отрицательных отзывов',
  `percent_dignity` tinyint(3) unsigned NOT NULL COMMENT 'Полезность комментария',
  `points` smallint(5) NOT NULL COMMENT 'Количество положительных баллов',
  `service_info` varchar(1024) NOT NULL COMMENT 'Cлужебная информация',
  `date_approved` datetime NOT NULL COMMENT 'Дата одобрения',
  `from_buyer` tinyint(3) unsigned NOT NULL COMMENT 'От купившего',
  `allow_html` tinyint(3) unsigned NOT NULL COMMENT 'Позволять html-теги',
  `favorite_for_author` tinyint(3) unsigned NOT NULL DEFAULT '0' COMMENT 'Избранный для автора',
  `is_answers_readed` tinyint(3) unsigned DEFAULT '0' COMMENT 'Ответы, добавленные после этого коммента прочитанны',
  `last_readed_answer_date` datetime NOT NULL DEFAULT '0000-00-00 00:00:00' COMMENT 'Дата последнего прочитанного комментария',
  PRIMARY KEY (`record_id`,`id`),
  KEY `id_index` (`id`),
  KEY `mark_index` (`mark`),
  KEY `parent_comment_id_index` (`parent_comment_id`),
  KEY `status_index` (`status`),
  KEY `user_id_index` (`user_id`),
  KEY `record_id_index` (`record_id`),
  KEY `favorite_for_author_index` (`favorite_for_author`),
  KEY `user_id_status_index` (`user_id`,`status`)
) ENGINE=InnoDB AUTO_INCREMENT=7519748 DEFAULT CHARSET=utf8 ROW_FORMAT=COMPRESSED
/*!50100 PARTITION BY HASH (record_id)
PARTITIONS 10 */
1 row in set (0.01 sec)

mysql>
mysql> select count(*) from goods_comments;
+----------+
| count(*) |
+----------+
| 2686398 |
+----------+
1 row in set (1.00 sec)

mysql> SELECT id, user_id FROM goods_comments WHERE user_id=197827 AND id=5273769;
Empty set (0.01 sec)

mysql> explain SELECT id, user_id FROM goods_comments WHERE user_id=197827 AND id=5273769;
+----+-------------+----------------+-------------+---------------------------------------------+------------------------+---------+------+------+-------------------------------------------------------------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+----------------+-------------+---------------------------------------------+------------------------+---------+------+------+-------------------------------------------------------------------+
| 1 | SIMPLE | goods_comments | index_merge | id_index,user_id_index,user_id_status_index | id_index,user_id_index | 4,3 | NULL | 1 | Using intersect(id_index,user_id_index); Using where; Using index |
+----+-------------+----------------+-------------+---------------------------------------------+------------------------+---------+------+------+-------------------------------------------------------------------+
1 row in set (0.00 sec)

mysql> SELECT id, user_id FROM goods_comments IGNORE INDEX (user_id_ind) WHERE user_id=197827 AND id=5273769;
+---------+---------+
| id | user_id |
+---------+---------+
| 5273769 | 197827 |
+---------+---------+
1 row in set (0.01 sec)

mysql> explain SELECT id, user_id FROM goods_comments IGNORE INDEX (user_id_ind) WHERE user_id=197827 AND id=5273769;
+----+-------------+----------------+------+-------------------------------+----------+---------+-------+------+-------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+----------------+------+-------------------------------+----------+---------+-------+------+-------------+
| 1 | SIMPLE | goods_comments | ref | id_index,user_id_status_index | id_index | 4 | const | 9 | Using where |
+----+-------------+----------------+------+-------------------------------+----------+---------+-------+------+-------------+
1 row in set (0.00 sec)