[MDEV-24711] Make "unique_key NOT IN (...)" conditions non-sargable Created: 2021-01-27  Updated: 2023-09-20  Resolved: 2021-01-27

Status: Closed
Project: MariaDB Server
Component/s: Optimizer
Affects Version/s: 10.2
Fix Version/s: 10.4.18, 10.5.9, 10.6.0

Type: Bug Priority: Major
Reporter: Sergei Petrunia Assignee: Sergei Petrunia
Resolution: Fixed Votes: 0
Labels: None

Issue Links:
PartOf
is part of MDEV-21958 Query having many NOT-IN clauses runn... Closed

 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.
Generated at Thu Feb 08 09:32:05 UTC 2024 using Jira 8.20.16#820016-sha1:9d11dbea5f4be3d4cc21f03a88dd11d8c8687422.