In this session, you learnt about some of the best practices that should be followed while writing SQL code. You also understood the importance of query optimisation and learnt about the factors that you need to consider while optimising queries. These factors are indexing, the order of query execution and the relative performance of joins as compared with that of nested queries for different scenarios. Let’s watch the upcoming video to revisit the topics that were covered in this session one by one.
In this video, Shreyas briefly revisited the topics that were covered in this session, which can be summarised as follows:
- Comment your code using a hyphen ‘-‘for a single line and ‘/* … */’ for multiple lines of code.
- Always use table aliases when your query involves more than one source table.
- Assign simple and descriptive names to columns and tables.
- Write SQL keywords in upper case and the names of columns, tables and variables in lower case.
- Always use column names in the ‘order by’ clause instead of numbers.
- Maintain the right indentation for different sections of a query.
- Use a new line for each column name.
- Use the SQL Formatter or the MySQL Workbench Beautification tool (Ctrl+B) to clean your code.
Indexing: Indexing is an effective way to optimise query execution, as it selects the required data values instead of processing the entire table. The syntaxes for creating, adding and dropping an index are as follows:
Clustered vs non-clustered indexing: The major differences between clustered and non-clustered indexing are summarised in the table given below.
| Clustered Index | Non-Clustered Index |
| This is mostly the primary key of the table. | It is a combination of one or more columns of the table. |
| It is present within the table. | The unique list of keys is present outside the table. |
| It does not require a separate mapping. | The external table points to different sections of the main table. |
| It is relatively faster. | It is relatively slower. |
Order of query execution: The order in which the different SQL statements are executed in a query is depicted in the diagram given below.
Order of Query Execution
Query optimisation techniques: The points that you should remember 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.
Joins vs nested queries: Executing a statement with the ‘join’ clause creates a join index, which is an internal indexing structure. This makes it more efficient than a nested query. However, a nested query would perform better than a join while querying data from a distributed database.