[
https://issues.apache.org/jira/browse/IGNITE-13316?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=17168522#comment-17168522
]
Ilya Kasnacheev commented on IGNITE-13316:
------------------------------------------
It is a known issue that only one index per table per query is used. In your
case, index on ver.id is used and index on ver.fixed_version is not.
You need to add index on issue_fixed_version (id, fixed_version) to get good
performance there.
> 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)
