Hi Andrew, 

I tried but unable to reproduce the same with PS 5.5.40 + partition table with hash + merge index. There is known bug related to this scenario but as per the comment, it should resolved in 5.5.36+ versions. http://bugs.mysql.com/bug.php?id=70588

I also found this bug related to intersect but I'm not sure it relates to your issue.  http://bugs.mysql.com/bug.php?id=72745
Is it possible for you to provide test data which we can use to reproduce this? Also can you try to check this with PS 5.5.40?


My test:

mysql> CREATE TABLE `goods_comments` (   `id` int(11) unsigned NOT NULL AUTO_INCREMENT COMMENT 'ID',   `text` text NOT NULL COMMENT 'x',   `created` datetime NOT NULL COMMENT 'x',   `ip` int(11) unsigned NOT NULL COMMENT 'x',   `status` tinyint(3) unsigned NOT NULL COMMENT 'x',   `record_id` mediumint(8) unsigned NOT NULL,   `name` varchar(64) NOT NULL COMMENT 'x',   `email` varchar(64) NOT NULL COMMENT 'x',   `user_id` mediumint(8) unsigned NOT NULL,   `mark` tinyint(3) unsigned DEFAULT NULL COMMENT 'x',   `rating` smallint(5) NOT NULL COMMENT 'x',   `count_voices` smallint(5) unsigned NOT NULL COMMENT 'x',   `summary` varchar(255) NOT NULL COMMENT 'x',   `dignity` text NOT NULL COMMENT 'x',   `shortcomings` text NOT NULL COMMENT 'x',   `parent_comment_id` int(11) unsigned NOT NULL,   `subscribed_on_replies` tinyint(3) unsigned NOT NULL COMMENT 'x',   `positive_vote_count` mediumint(8) unsigned NOT NULL COMMENT 'x',   `negative_vote_count` mediumint(8) unsigned NOT NULL COMMENT 'x',   `percent_dignity` tinyint(3) unsigned NOT NULL COMMENT 'x',   `points` smallint(5) NOT NULL COMMENT 'x',   `service_info` varchar(1024) NOT NULL COMMENT 'x',   `date_approved` datetime NOT NULL COMMENT 'x',   `from_buyer` tinyint(3) unsigned NOT NULL COMMENT 'x',   `allow_html` tinyint(3) unsigned NOT NULL COMMENT 'x',   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`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8 ROW_FORMAT=COMPRESSED /*!50100 PARTITION BY HASH (record_id) PARTITIONS 10 */;
Query OK, 0 rows affected (0.05 sec)


mysql> insert into goods_comments(text,created,ip,status,record_id,name,email,user_id,mark,rating,count_voices,summary,dignity,shortcomings,parent_comment_id,subscribed_on_replies,positive_vote_count,negative_vote_count,percent_dignity,points,service_info,date_approved,from_buyer,allow_html) values ('abcdeef',now(), 123455677,1,123,'nilnandan','<email address hidden>',12345,2,4,5,'sdfsdfsdf','sdfsdf','sdfsdf',123,123,1,23,34,34,'asdasd',now(),5,6);
Query OK, 1 row affected (0.00 sec)

mysql> insert into goods_comments(text,created,ip,status,record_id,name,email,user_id,mark,rating,count_voices,summary,dignity,shortcomings,parent_comment_id,subscribed_on_replies,positive_vote_count,negative_vote_count,percent_dignity,points,service_info,date_approved,from_buyer,allow_html) values ('abcdeef',now(), 123455677,1,123,'nilnandan','<email address hidden>',14565,2,4,5,'sdfsdfsdf','sdfsdf','sdfsdf',123,123,1,23,34,34,'asdasd',now(),5,6);
Query OK, 1 row affected (0.01 sec)

mysql> insert into goods_comments(text,created,ip,status,record_id,name,email,user_id,mark,rating,count_voices,summary,dignity,shortcomings,parent_comment_id,subscribed_on_replies,positive_vote_count,negative_vote_count,percent_dignity,points,service_info,date_approved,from_buyer,allow_html) values ('abcdeef',now(), 123455677,1,123,'nilnandan','<email address hidden>',34565,2,4,5,'sdfsdfsdf','sdfsdf','sdfsdf',123,123,1,23,34,34,'asdasd',now(),5,6);
Query OK, 1 row affected (0.01 sec)

mysql> insert into goods_comments(text,created,ip,status,record_id,name,email,user_id,mark,rating,count_voices,summary,dignity,shortcomings,parent_comment_id,subscribed_on_replies,positive_vote_count,negative_vote_count,percent_dignity,points,service_info,date_approved,from_buyer,allow_html) values ('abcdeef',now(), 123455677,1,123,'nilnandan','<email address hidden>',23565,2,4,5,'sdfsdfsdf','sdfsdf','sdfsdf',123,123,1,23,34,34,'asdasd',now(),5,6);
Query OK, 1 row affected (0.01 sec)

mysql> insert into goods_comments(text,created,ip,status,record_id,name,email,user_id,mark,rating,count_voices,summary,dignity,shortcomings,parent_comment_id,subscribed_on_replies,positive_vote_count,negative_vote_count,percent_dignity,points,service_info,date_approved,from_buyer,allow_html) values ('abcdeef',now(), 123455677,1,123,'nilnandan','<email address hidden>',197827,2,4,5,'sdfsdfsdf','sdfsdf','sdfsdf',123,123,1,23,34,34,'asdasd',now(),5,6);
Query OK, 1 row affected (0.01 sec)


mysql> insert into goods_comments(text,created,ip,status,record_id,name,email,user_id,mark,rating,count_voices,summary,dignity,shortcomings,parent_comment_id,subscribed_on_replies,positive_vote_count,negative_vote_count,percent_dignity,points,service_info,date_approved,from_buyer,allow_html) select text,created,ip,status,record_id,name,email,user_id,mark,rating,count_voices,summary,dignity,shortcomings,parent_comment_id,subscribed_on_replies,positive_vote_count,negative_vote_count,percent_dignity,points,service_info,date_approved,from_buyer,allow_html from goods_comments;
Query OK, 4 rows affected (0.01 sec)
Records: 4  Duplicates: 0  Warnings: 0
..
..
...
....

mysql> insert into goods_comments(text,created,ip,status,record_id,name,email,user_id,mark,rating,count_voices,summary,dignity,shortcomings,parent_comment_id,subscribed_on_replies,positive_vote_count,negative_vote_count,percent_dignity,points,service_info,date_approved,from_buyer,allow_html) select text,created,ip,status,record_id,name,email,user_id,mark,rating,count_voices,summary,dignity,shortcomings,parent_comment_id,subscribed_on_replies,positive_vote_count,negative_vote_count,percent_dignity,points,service_info,date_approved,from_buyer,allow_html from goods_comments;

Query OK, 524292 rows affected (30.26 sec)
Records: 524292  Duplicates: 0  Warnings: 0

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

mysql> select count(*) from goods_comments where user_id=197827;
+----------+
| count(*) |
+----------+
|        8 |
+----------+
1 row in set (0.00 sec)

mysql> select count(*) from goods_comments where id=1048584;
+----------+
| count(*) |
+----------+
|        1 |
+----------+
1 row in set (0.00 sec)

mysql> 

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

mysql> EXPLAIN SELECT id, user_id FROM goods_comments WHERE user_id=197827 AND id=1048584;
+----+-------------+----------------+-------------+------------------------+------------------------+---------+------+------+-------------------------------------------------------------------+
| id | select_type | table          | type        | possible_keys          | key                    | key_len | ref  | rows | Extra                                                             |
+----+-------------+----------------+-------------+------------------------+------------------------+---------+------+------+-------------------------------------------------------------------+
|  1 | SIMPLE      | goods_comments | index_merge | id_index,user_id_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.01 sec)

mysql>