SQL tips for performance optimization

  1. SQL query order of operations:

Source: https://www.periscopedata.com/blog/sql-query-order-of-operations

  1. WHERE clause is iterated on each row
    -> subquery should not be placed in WHERE
    -> The more complicated the WHERE clause is, the lower the performance the SQL query has, especially when the row numbers are large enough.

  2. FROM:
    -> minimize the data to load from FROM
    -> If JOIN with 1 subquery needs to be made, only SELECT necessary columns in the subquery
    -> Avoid shortening SQL query by creating middle query
    -> Base table should be load from FROM rather than the other query result

  3. Cases of JOIN: