Comment 6 for bug 1405155

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>