Xie Bo created IGNITE-13316: ------------------------------- Summary: 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: springdata Affects Versions: 2.8.1 Reporter: Xie Bo
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*s) ________________________________________ 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? Any way 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)