Query Planner chooses wrong index for ORDER BY ... LIMIT
Affects | Status | Importance | Assigned to | Milestone | |
---|---|---|---|---|---|
Percona Server moved to https://jira.percona.com/projects/PS |
New
|
Undecided
|
Unassigned |
Bug Description
Used Percona mysqld Ver 5.6.19-67.0 for debian-linux-gnu on x86_64 (Percona Server (GPL), Release 67.0, Revision 618)
This is probably an upstream bug already (as it's clearly a query planner fail), but as we're using Percona I probably better report it here to avoid discussions with the MySQL maintainers on bug reports for software not "their own". Maybe you can report this upstream or so, don't know how issues like there are coordinated across the different MySQL forks.
We have a table
CREATE TABLE `suggestion` (
`user_id` int(10) unsigned NOT NULL,
`type` enum('user'
`obj_id` int(10) unsigned NOT NULL,
`rejected` enum('y','n') COLLATE latin1_german2_ci NOT NULL DEFAULT 'n',
`flags` int(10) unsigned NOT NULL DEFAULT '0',
`data` varbinary(32768) NOT NULL DEFAULT '',
`created` timestamp NOT NULL DEFAULT '0000-00-00 00:00:00',
PRIMARY KEY (`user_
KEY `user_id` (`user_
KEY `type` (`type`,`created`),
KEY `rejected` (`rejected`
) ENGINE=InnoDB DEFAULT CHARSET=latin1 COLLATE=
The table is about 12 GB of data and 40 million rows.
The query:
SELECT obj_id, flags, data, type FROM suggestion WHERE user_id='7723036' AND type IN ('newbie', 'group') AND rejected='n' AND flags=0 ORDER BY type DESC, created DESC LIMIT 50;
takes about 4 seconds on our system since a Percona 5.6 upgrade from 5.1 as the wrong index ('type') is chosen by the query planner instead of the more optimal PRIMARY KEY. Simply leaving out the "LIMIT 40" fixes the problem - then the proper index for that SELECT (the PRIMARY) will be used. We're currently working around the issue with a FORCE INDEX, but it'd be nice, if the problem could be properly fixed in the planner itself. The query planner obviously re-evaluates the chosen plan due to a low LIMIT value and then chooses a plan with a very high cost as evident from the plan trace:
Query Plan Trace is here:
| SELECT obj_id, flags, data, type FROM suggestion WHERE user_id='7723036' AND type IN ('newbie', 'group') AND rejected='n' AND flags=0 ORDER BY type DESC, created DESC LIMIT 50 | {
"steps": [
{
"
"select#": 1,
"steps": [
{
}
]
}
},
{
"
"select#": 1,
"steps": [
{
{
},
{
},
{
}
]
}
},
{
{
]
}
]
},
{
{
},
{
},
{
},
{
}
]
},
{
{
}
}
}
]
},
{
{
],
]
},
}
]
},
{
{
}
}
],
{
}
]
}
},
{
{
},
{
}
],
}
},
{
{
}
]
},
{
}
}
}
]
}
},
{
"
"select#": 1,
"steps": [
]
}
}
]
} | 0 | 0 |
Changed in percona-server: | |
assignee: | nobody → Muhammad Irfan (muhammad-irfan) |
Changed in percona-server: | |
status: | Incomplete → New |
Changed in percona-server: | |
assignee: | nobody → Muhammad Irfan (muhammad-irfan) |
Changed in percona-server: | |
status: | Incomplete → New |
This is probably a yet another consequence of bad fix for http:// bugs.mysql. com/bug. php?id= 28404 that still remains (and gives preference to index that allows to avoid ORDER BY with LIMIT N when N is small). it is hard to say is it a new or known bug, at least without a complete repeatable test case.
I'll try to create one and report upstream bug for it, and then let Oracle engineers decide.