0

If I have

Select *  
from tbl_x x  
left join tbl_y y on x.a = y.a  
where y.b = 'iwantthisvalue'

should I create separate index for each idx(y.a) and idx2(y.b) or should I create only one with 2 columns idx(y.a, y.b)?

philipxy
  • 14,867
  • 6
  • 39
  • 83
Dyaksa Hanindito
  • 143
  • 8
  • I would say idx(y.b, y.a) would be better since y.b is in where condition which will be used to filter rows. – Ankit Bajpai Jun 16 '22 at 10:07
  • 2
    Ask your database using EXPLAIN(ANALYZE, VERBOSE, BUFFERS) for your SQL - statements. This will show you the query plan and execution time and gives you something to compare. – Frank Heikens Jun 16 '22 at 10:07
  • 2
    By the way, because of your WHERE condition the LEFT JOIN turns into an INNER JOIN. – Frank Heikens Jun 16 '22 at 10:07
  • [How to Optimize Queries in a Database - The Basics](https://stackoverflow.com/q/3191623/3404097) [What are your most common sql optimizations?](https://stackoverflow.com/q/1332778/3404097) [When and why are database joins expensive?](https://stackoverflow.com/q/173726/3404097) [Re SQL "performance".](https://stackoverflow.com/a/24196511/3404097) [Tips for asking a good SQL question](https://meta.stackoverflow.com/q/271055/3404097) [Asking query performance questions](https://dba.meta.stackexchange.com/q/3034/43932) – philipxy Jun 16 '22 at 10:31

1 Answers1

1

If there are multiple rows with same value in y.a and multiple rows with same value in y.b - you can create one index for both create index on y(a asc, b asc). The executor will look up first for join and then for where in joined result. If there are aggregate functions in select - add other columns in include of the index. The group by columns could also be "added" (not "included") to the index if it is used in the query. So the executor will look up for join after that for where, for group by and for select of "included".

RomanG
  • 230
  • 1
  • 8