Not the answer you need?
Register and ask your own question!

Percona MySQL Index Bug

sqlercnsqlercn Current User Role Beginner
My table schema is 
CREATE TABLE test (
  `id` int(11) unsigned NOT NULL AUTO_INCREMENT ,
  `class_id` int(11) unsigned NOT NULL DEFAULT '0' ,
  `stu_id` int(11) unsigned NOT NULL DEFAULT '0' ,
  `work_id` int(11) unsigned NOT NULL DEFAULT '0',
  `img_id` int(11) unsigned NOT NULL DEFAULT '0' ,
  `commit_id` int(11) unsigned NOT NULL DEFAULT '0' ,
  `test_id` int(11) unsigned NOT NULL DEFAULT '0',
  `audio_ids` varchar(500) NOT NULL DEFAULT '0' ,
  `correct_time` datetime NOT NULL DEFAULT '0000-00-00 00:00:00' ,
  `is_wrong` tinyint(1) unsigned NOT NULL DEFAULT '0' ,
  `status` tinyint(1) unsigned NOT NULL DEFAULT '0' ,
  PRIMARY KEY (`id`),
  KEY `commit_id` (`commit_id`),
  KEY `correct_time` (`correct_time`),
  KEY `commit_test` (`commit_id`,`test_id`)
) ENGINE=InnoDB AUTO_INCREMENT=242058182 
my query this table for commit_id = 93317439 and commit_id = 93317438 
explain select count(*) from test  where commit_id=93317438
+----+-------------+------------------------+------------+------+---------------+-----------+---------+-------+------
| id | select_type | table                  | partitions | type | possible_keys | key       | key_len | ref   | rows | filtered | Extra    
+----+-------------+------------------------+------------+------+---------------+-----------+---------+-------+------
|  1 | SIMPLE      | test| NULL       | ref  | commit_id     | commit_id | 4       | const |    1 |   100.00 | Using index |
+----+-------------+------------------------+------------+------+---------------+-----------+---------+-------+------
explain select count(*) from test  where commit_id=93317439
+----+-------------+------------------------+------------+------+-----------------------+-----------+---------+-------
| id | select_type | table  | partitions | type | possible_keys         | key       | key_len | ref   | rows     | filtered | Extra       |
+----+-------------+------------------------+------------+------+-----------------------+-----------+---------+-------|  1 | SIMPLE      | test| NULL       | ref  | commit_id,commit_test | commit_id | 4       | const | 44587972 |   100.00 | Using index |
+----+-------------+------------------------+------------+------+-----------------------+-----------+---------+-------
but commit_id=93317439 only 2 rows

Answers

  • lorraine.pocklingtonlorraine.pocklington Percona Community Manager Legacy User Role Patron
    Hello @sqlercn
    What were you expecting to see here and how does it differ from the result you had? 
Sign In or Register to comment.

MySQL, InnoDB, MariaDB and MongoDB are trademarks of their respective owners.
Copyright ©2005 - 2020 Percona LLC. All rights reserved.