Details
-
Bug
-
Status: Closed (View Workflow)
-
Major
-
Resolution: Fixed
-
10.2
-
None
Description
This is a part of MDEV-21958.
This MDEV covers "(Very basic) Make NOT-IN non-sargable" suggestion from here:
https://jira.mariadb.org/browse/MDEV-21958?focusedCommentId=170957&page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel#comment-170957
The idea is to make conditions in form "unique_key NOT IN (const, .... )" non-sargable. The reasoning is: when the constant list is small, the condition is not selective. when the constant list is large (half the table or more), the overhead of processing the list by the optimizer is too high.
The fix was pushed as these three patches:
commit dcc7f93965f7fb534f29c5c682b2abf22e808fe2
|
Author: Sergei Petrunia <psergey@askmonty.org>
|
Date: Fri Dec 11 22:45:54 2020 +0300
|
|
|
MDEV-21958: Query having many NOT-IN clauses running forever, part 3
|
|
Add the new file
|
commit 502bc77f23715f84a049fe6c28a861e9af271016
|
Author: Sergei Petrunia <psergey@askmonty.org>
|
Date: Fri Dec 11 22:44:13 2020 +0300
|
|
|
MDEV-21958: Query having many NOT-IN clauses running forever, part 2
|
|
Move the testcase into a separate file: embedded server
|
doesn't have optimizer trace.
|
commit 4addd31531f722438b8b702c9cd00c28b61efce3
|
Author: Sergei Petrunia <psergey@askmonty.org>
|
Date: Fri Dec 11 18:54:21 2020 +0300
|
|
|
MDEV-21958: Query having many NOT-IN clauses running forever
|
|
Basic variant of the fix: do not consider conditions in form
|
|
unique_key NOT IN (c1,c2...)
|
|
to be sargable. If there are only a few constants, the condition
|
is not selective. If there are a lot constants, the overhead of
|
processing such a huge range list is not worth it.
|
The fixes were pushed into 10.4, 10.5, and 10.6 trees.
Attachments
Issue Links
- is part of
-
-
- Closed
-
- mentioned in
-
Page Loading...
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-