Details
-
Bug
-
Status: Closed (View Workflow)
-
Major
-
Resolution: Fixed
-
10.5, 10.6, 10.11
-
None
Description
InnoDB is known to produce overly-optimistic index estimates for index lookups.
This was done intentionally, see this piece in ha_innobase::info_low:
/* Since MySQL seems to favor table scans |
too much over index searches, we pretend
|
index selectivity is 2 times better than
|
our estimate: */
|
|
|
rec_per_key_int = rec_per_key_int / 2;
|
However in some cases that can cause poor query plans.
in 11.0, this was removed by this commit:
commit 01c82173dd8b6ea627dec949dbc57dfcc94a656d
|
Author: Monty <monty@mariadb.org>
|
Date: Thu Jan 26 11:38:31 2023 +0200
|
|
|
Removed /2 of InnoDB ref_per_key[] estimates
|
|
The original code was there to favor index search over table scan.
|
This is not needed anymore as the cost calculations for table scans
|
and index lookups are now more exact.
|
This MDEV is about adding a capability to remove the /2 in versions before 11.0
Attachments
Activity
| Field | Original Value | New Value |
|---|---|---|
| Status | Open [ 1 ] | In Progress [ 3 ] |
| Assignee | Sergei Petrunia [ psergey ] | Michael Widenius [ monty ] |
| Affects Version/s | 10.5 [ 23123 ] | |
| Affects Version/s | 10.11 [ 27614 ] |
| Description |
InnoDB is known to produce overly-optimistic index estimates for index lookups.
This was done intentionally, see this piece in {{ha_innobase::info_low}}: {code:cpp} /* Since MySQL seems to favor table scans too much over index searches, we pretend index selectivity is 2 times better than our estimate: */ rec_per_key_int = rec_per_key_int / 2; {code} However in some cases that can cause poor query plans. |
InnoDB is known to produce overly-optimistic index estimates for index lookups.
This was done intentionally, see this piece in {{ha_innobase::info_low}}: {code:cpp} /* Since MySQL seems to favor table scans too much over index searches, we pretend index selectivity is 2 times better than our estimate: */ rec_per_key_int = rec_per_key_int / 2; {code} However in some cases that can cause poor query plans. in 11.0, this was removed by this commit: {code} commit 01c82173dd8b6ea627dec949dbc57dfcc94a656d Author: Monty <monty@mariadb.org> Date: Thu Jan 26 11:38:31 2023 +0200 Removed /2 of InnoDB ref_per_key[] estimates The original code was there to favor index search over table scan. This is not needed anymore as the cost calculations for table scans and index lookups are now more exact. {code} This MDEV is about adding a capability to remove the /2 in versions before 11.0 |
| Assignee | Michael Widenius [ monty ] | Sergei Petrunia [ psergey ] |
| Fix Version/s | 10.6.19 [ 29833 ] | |
| Fix Version/s | 10.11.9 [ 29834 ] | |
| Fix Version/s | 11.1.6 [ 29835 ] | |
| Fix Version/s | 11.2.5 [ 29836 ] | |
| Fix Version/s | 11.4.3 [ 29837 ] | |
| Fix Version/s | 11.5.2 [ 29838 ] | |
| Fix Version/s | 11.6.1 [ 29847 ] | |
| Fix Version/s | 10.6 [ 24028 ] | |
| Resolution | Fixed [ 1 ] | |
| Status | In Progress [ 3 ] | Closed [ 6 ] |
https://github.com/MariaDB/server/pull/3432