MySQL Bugs: #83298: MYSQL 5.6.10 Optimiser selecting wrong index

Bug #83298	MYSQL 5.6.10 Optimiser selecting wrong index
Submitted:	7 Oct 2016 9:52	Modified:	20 Oct 2016 12:50
Reporter:	masood alam	Email Updates:
Status:	Duplicate	Impact on me:	None
Category:	MySQL Server: Optimizer	Severity:	S2 (Serious)
Version:	5.6.10	OS:	Debian (debian-linux-gnu (x86_64) using readline 6.2)
Assigned to:		CPU Architecture:	Any
Tags:	MySQL, optimiser

Description:
Hey,

Structure of the Table
-----------------------------

CREATE TABLE `seat` (
  `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
  `table_id` bigint(20) DEFAULT NULL,
  `user_id` bigint(20) DEFAULT NULL,
  `create_date` datetime DEFAULT NULL,
  `game_id` bigint(20) DEFAULT NULL,
  `status` tinyint(4) DEFAULT NULL,
  `code` varchar(12) DEFAULT NULL,
  `outcome` tinyint(4) DEFAULT NULL,
  `finalscore` decimal(7,2) DEFAULT NULL,
  `stake` int(11) DEFAULT NULL,
  `winnings` decimal(10,2) DEFAULT NULL,
  `data` text,
  `lifecycle_notifications` int(11) DEFAULT NULL,
  `is_public` tinyint(1) DEFAULT NULL,
  `finalpos` int(11) DEFAULT NULL,
  `commission` decimal(10,2) DEFAULT NULL,
  `pot` decimal(10,2) DEFAULT NULL,
  `table_spec_id` bigint(20) DEFAULT NULL,
  `updated_date` datetime DEFAULT NULL,
  `roster_id` bigint(20) DEFAULT NULL,
  `entrysource` tinyint(4) DEFAULT NULL,
  `houseclaimed` tinyint(1) DEFAULT NULL,
  `bonus_earned` decimal(6,2) DEFAULT NULL,
  `stake_currency_type` tinyint(4) DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `game_id_idx` (`game_id`,`status`) USING HASH,
  KEY `code_idx` (`code`) USING HASH,
  KEY `seat_updated_date` (`updated_date`),
  KEY `ixSeatRoster` (`roster_id`),
  KEY `user_status_game` (`user_id`,`status`,`game_id`),
  KEY `table_finalpos` (`table_id`,`finalpos`),
  KEY `user_create_status_idx` (`user_id`,`create_date`,`status`)
) ENGINE=InnoDB 

Number of rows in the table
---------------------------

550 Million

Query explained
------------------------

As you can see form the query explain output that optimiser knows there is an index but unfortunately it still chooses the wrong index. You can see the index defined above as game_id_idx.

mysql> explain select s.id,s.game_id,s.status,s.roster_id from seat s where game_id = 16073 order by id limit 10001;
+----+-------------+-------+-------+---------------+---------+---------+------+---------+-------------+
| id | select_type | table | type  | possible_keys | key     | key_len | ref  | rows    | Extra       |
+----+-------------+-------+-------+---------------+---------+---------+------+---------+-------------+
|  1 | SIMPLE      | s     | index | game_id_idx   | PRIMARY | 8       | NULL | 1095256 | Using where |
+----+-------------+-------+-------+---------------+---------+---------+------+---------+-------------+
1 row in set (0.00 sec)

Thanks

Masood Alam

How to repeat:
Solution
--------

If we use force index in the query then optimiser is able to work with correct index, here is an example below

explain select s.id,s.game_id,s.status,s.roster_id from seat s force index (game_id_idx) where game_id = 16073 order by id limit 10001;
+----+-------------+-------+------+---------------+-------------+---------+-------+---------+-----------------------------+
| id | select_type | table | type | possible_keys | key         | key_len | ref   | rows    | Extra                       |
+----+-------------+-------+------+---------------+-------------+---------+-------+---------+-----------------------------+
|  1 | SIMPLE      | s     | ref  | game_id_idx   | game_id_idx | 9       | const | 4995154 | Using where; Using filesort |
+----+-------------+-------+------+---------------+-------------+---------+-------+---------+-----------------------------+
1 row in set (0.01 sec)

Although force index is working but our application is huge and it is difficult to know when and where optimiser will stop using the correct index.

Suggested fix:
Currently a hack is to use force index as explained above.

detail optimiser trace attached

Attachment: optimiserbug.txt (text/plain), 20.07 KiB.

Thank you for the bug report. Your server version 5.6.10 is quite older current release is 5.6.34 please when filling bugs test first with the latest release. Thanks in advance.

Hi,

I have tested this with various versions including MYSQL 5.7, MariaDB 10.1 and AMAZON Aurora. All versions of MYSQL and MYSQL forks behaves the same.

Thanks

Masood

Lead Data Architect 

FanDuel LTD

Thank you for the feedback. Wonder if related to https://bugs.mysql.com/bug.php?id=78651

Hi,

I think my query is some what different to the case you mentioned. The other bug did not provided optimiser trace, so I cannot compare the detail plan

Hello Masood,

This is the same problem as found in Bug #78651 i.e Primary key on id chosen instead of composite index on game_id and another one column and thus which leads to processing more rows than needed and wrong EXPLAIN output. Even if you feel that your issue is somewhat different, the resolution is likely
to be the same. 

Also, please try with latest GA build as 5.6.10 is very old and since then many bugs fixed.  

Thanks,
Umesh

Hello,

Thanks for your reply. If you consider this as a duplicate then its great, at least I am assured of the fact that you already know of this.

Secondly I have tried with MYSQL 5.6.34 and I have also tried with MYSQL 5.7.16, in all of my tests the result is the same. The fact that it even appears in MYSQL 5.7 tells me that the optimiser bug was carried forward to newer MYSQL 5.7

Thanks

Masood