Suppose you are an aspiring singer who wants to make it in the music industry. In order to start your journey, you register to enter the reality TV show Indian Idol. You find yourself waiting in the line for hours with thousands of other contestants. After interacting with some of them, you realise that most of the contestants have no sense of tune, rhythm or music in general.
Can you think of a way in which you can make it easier for the judges to filter the applications? Think about it, and watch the next video to find out what Shreyas thinks is a feasible solution to this problem.
In this video, Shreyas explained one of the methods to filter the applications. In the module ‘Database Design and Introduction to SQL’, you learnt about the different aspects of a query, which appear in a particular order. The order in which the various SQL statements appear in a query is as follows:
- SELECT
- FROM
- [JOIN]
- WHERE
- GROUP BY
- HAVING
- WINDOW
- ORDER BY
However, the order in which the various statements are executed by the database engine is not the same. In the upcoming video, you will learn about the order of query execution, which will help you understand the need for reducing your data set as much as possible before querying it.
In this video, you learnt about the order in which the different SQL statements are executed in a query, which is depicted in the diagram given below.
Some of the important points that you should keep in mind while writing a query are as follows:
- Use inner joins wherever possible to avoid having any unnecessary rows in the resultant table.
- Apply all the required filters to get only the required data values from multiple tables.
- Index the columns that are frequently used in the WHERE clause.
- Avoid using DISTINCT while using the GROUP BY clause, as it slows down query .processing.
- Avoid using SELECT * as much as possible. Select only the required columns.
- Use the ORDER BY clause only if it is absolutely necessary, as it is processed late in a query.
- Avoid using LIMIT and OFFSET as much as possible. Instead, apply appropriate filters using the WHERE clause.
Now that you know the order of query execution in SQL, let’s see a practical example of how this can be useful. In the next segment, you will reduce the run time of a query by applying some of the optimisation techniques that you have learnt so far.