Bug #93523 Implement exists-to-in correlated subquery optimization

• Submitted: 7 Dec 2018 19:45
• Modified: 14 Dec 2018 16:25
• Reporter: Geoff Montee
• Status: Closed
• Category: MySQL Server: Optimizer
• Severity: S4 (Feature request)
• Version: 5.6,5.7,8.0
• OS: Any
• CPU Architecture: Any
• Tags: exists, IN, optimization, Optimizer, optimizer_switch, performance, semi-join, subquery

[7 Dec 2018 19:45] Geoff Montee

Description:
Queries that use EXISTS() with a correlated subquery do not always perform optimally, so they often have to be manually converted to use IN() instead. MariaDB worked around this problem by adding the exists_to_in optimizer_switch that would allow the optimizer to convert correlated subqueries that use EXISTS() to use IN() instead when applicable. See the following pages for more information:

http://s.petrunia.net/blog/?p=76

https://mariadb.com/kb/en/library/exists-to-in-optimization/

Sergei Petrunia's blog post on the topic contains the following note:

"IIRC MySQL/Sun also had a task for adding EXISTS->IN rewrite, also around 2009. I don’t know what the fate of that task was. It is definitely not in MySQL 5.6, and google doesn’t find the worklog entry, I suppose because it has been made private. "

Is a similar feature coming to MySQL at some point? I don't see a similar optimization listed for 8.0:

https://dev.mysql.com/doc/refman/8.0/en/switchable-optimizations.html

How to repeat:
Use EXISTS() with a correlated subquery.

Suggested fix:
Convert EXISTS() to IN() when applicable.

[14 Dec 2018 16:25] MySQL Verification Team

Hi,

Thank you for your report.

Your feature request is well intended .......

However, WorkLog # 4389 has already being pushed into 8.0.15.

This WorkLog entry covers some optimisations and some switches, which are completely covering the feature that you have asked for in this report.

Thank you.