Bug #106654 | join on json key can not use index, but join on virtual column can use index | ||
---|---|---|---|
Submitted: | 7 Mar 2022 12:06 | Modified: | 10 Mar 2022 13:52 |
Reporter: | mingsen xie | Email Updates: | |
Status: | Verified | Impact on me: | |
Category: | MySQL Server: Optimizer | Severity: | S5 (Performance) |
Version: | 8.0 | OS: | Any |
Assigned to: | CPU Architecture: | Any |
[7 Mar 2022 12:06]
mingsen xie
[7 Mar 2022 13:01]
mingsen xie
CAST(prole.id AS CHAR) change to CONVERT(prole.id using utf8mb4) also not work
[8 Mar 2022 14:01]
MySQL Verification Team
Hi Mr. xie, Thank you for your bug report. However, this is not a bug. You are using functions in your join query which, regardless of the data type, prevents totally usage of indices . However, virtual column that uses that function can be used, since its index is built with that function included. This is all described in our Reference Manual. Not a bug.
[10 Mar 2022 13:52]
MySQL Verification Team
HI Mr. xie, We have analysed further your report and it turns out that there is a deviation in the LEFT JOIN, which does not exist with an inner join. For that reason, this report is now a verified bug.