Indexing Fundamentals: Indexing for Joins

Follow the below guidelines as a reference for creating Indexes for queries having JOINs.

Single JOIN

  • 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.

WHERE Exists:

  • 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.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s