Follow the below guidelines as a reference for creating Indexes for queries having JOINs.
- Generally the smaller table is hit first when there is no WHERE Clause
Single JOIN with WHERE
- When comibing JOIN with WHERE think of JOIN like a Where only.
- Better to break the join and WHERE and improve the performance
JOIN with ORDER BY
- It could not be possible to avoid SORT and it will depend upon the query, as the data is coming from different tables and joined on different column the output could be sorted but only per true join value.
Mixing JOINS And Filters
- Position of WHERE doesn’t matter in JOIN, focus on readability
Lots OF JOINS
- Which table will it first go to will depend upon the selectivity of the column with the filter, the number of pages in the tables, and the indexes are in shape or not and also order of the data coming out of it.
5 by 5 rule: 5 indexes per table and 5 columns per index is the common best practise.
- Exists is also like a JOIN
Joins are like filters, and their selectivity is row count + size. If we have sorted the data on join
Writing Queries for readability
- If it defines the relationship between the tables put it in a Join
- It it’s a filter which is just filtering the data that you want to see put it in a WHERE clause.