[
https://issues.apache.org/jira/browse/IGNITE-13316?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=17169266#comment-17169266
]
Xie Bo commented on IGNITE-13316:
---------------------------------
Can Ignite optimize the LEFT JOIN to INNER JOIN if the {{WHERE}} condition is
always false for the generated {{NULL}} row? e.g.,, the below 2 SQLs have the
same result but the performance is (100X) different in Ignite .
SELECT * FROM issue *LEFT* JOIN issue_fixed_version ver ON issue.id=ver.id
WHERE ver.FIXED_VERSION = '4.15';
12 rows selected (*2.015 second*)
________________________________________
SELECT * FROM issue *INNER* JOIN issue_fixed_version ver ON issue.id=ver.id
WHERE ver.FIXED_VERSION = '4.15';
12 rows selected (*0.022 second*)
________________________________________
According to
[https://dev.mysql.com/doc/refman/8.0/en/outer-join-optimization.html], MySQL
can convert LEFT JOIN to INNER JOIN if the {{WHERE}} condition is always false
for the generated {{NULL}} row. Can Ignite do the similar optimization？
----------
For a {{LEFT JOIN}}, if the {{WHERE}} condition is always false for the
generated {{NULL}} row, the {{LEFT JOIN}} is changed to an inner join. For
example, the {{WHERE}} clause would be false in the following query if
{{t2.column1}} were {{NULL}}:
{{SELECT * FROM t1 LEFT JOIN t2 ON (column1) WHERE t2.column2=5;}}
Therefore, it is safe to convert the query to an inner join:
{{SELECT * FROM t1, t2 WHERE t2.column2=5 AND t1.column1=t2.column1;}}
----------
> The left join table's index is not used in Ignite SQL execution
> ---------------------------------------------------------------
>
> Key: IGNITE-13316
> URL: https://issues.apache.org/jira/browse/IGNITE-13316
> Project: Ignite
> Issue Type: Improvement
> Components: sql
> Affects Versions: 2.8.1
> Reporter: Xie Bo
> Priority: Major
>
>
> SELECT * FROM issue LEFT JOIN issue_fixed_version ver ON issue.id=ver.id
> WHERE *issue.id in (select distinct id from issue_fixed_version where
> fixed_version = '4.15')*;
> 12 rows selected (*0.062 seconds*)
> ________________________________________
> SELECT * FROM issue LEFT JOIN issue_fixed_version ver ON issue.id=ver.id
> WHERE *ver.FIXED_VERSION = '4.15'*;
> 12 rows selected (*2.015 second*)
> ________________________________________
> Note: 'issue' is a table with over a million rows and 'issue_fixed_version'
> is another table with much less rows. There is an index on 'FIXED_VERSION'
> column in 'issue_fixed_version' table.
> The above 2 SQLs do the same thing but have different performance. The first
> SQL's explain plan shows that the index (on 'FIXED_VERSION' column in
> 'issue_fixed_version' table) is used and then its performance is good; the
> second SQL's explain plan shows that the index (on 'FIXED_VERSION' column in
> 'issue_fixed_version' table) is not used, why? Is there any configuration
> option to force the second SQL to use the index (on 'FIXED_VERSION' column in
> 'issue_fixed_version' table)?
--
This message was sent by Atlassian Jira
(v8.3.4#803005)
