Here is the list of the top 20 SQL query optimization techniques I found important:
1. Create an index on very large tables (>1.000.000) rows
2. Use EXIST() instead of COUNT() to find an element in the table
3. SELECT fields instead of using SELECT *
4. Avoid Subqueries in WHERE Clause
5. Avoid SELECT DISTINCT where possible
6. Use WHERE Clause instead of HAVING
7. Create joins with INNER JOIN (not WHERE)
8. Use LIMIT to sample query results
9. Use UNION ALL instead of UNION wherever possible
10. Use UNION where instead of WHERE … or … query.
11. Run your query during off-peak hours
12. Avoid using OR in join queries
14. Choose GROUP BY over window functions
15. Use derived and temporary tables
16. Drop the index before loading bulk data
16. Use materialized views instead of views
17. Avoid != or <> (not equal) operator
18. Minimize the number of subqueries
19. Try to use INNER join as little as possible when you can get the same output using LEFT/RIGHT join.
20. For retrieving the same dataset frequently try to use temporary sources.
Do you know what is ๐ค๐๐ฒ๐ฟ๐ ๐ข๐ฝ๐๐ถ๐บ๐ถ๐๐ฒ๐ฟ? Its primary function is to determine ๐๐ต๐ฒ ๐บ๐ผ๐๐ ๐ฒ๐ณ๐ณ๐ถ๐ฐ๐ถ๐ฒ๐ป๐ ๐๐ฎ๐ to execute a given SQL query by finding the best execution plan. The query optimizer works by taking the SQL query as input and analyzing it to determine how best to execute it. The first step is to parse the SQL query and create a syntax tree. The optimizer then analyzes the syntax tree to determine the various ways the query can be executed.
Next, the optimizer generates ๐ฎ๐น๐๐ฒ๐ฟ๐ป๐ฎ๐๐ถ๐๐ฒ ๐ฒ๐ ๐ฒ๐ฐ๐๐๐ถ๐ผ๐ป ๐ฝ๐น๐ฎ๐ป๐, which are different ways of executing the same query. Each execution plan specifies the order in which the tables should be accessed, the join methods to use, and any filtering or sorting operations to be performed. The optimizer then assigns a ๐ฐ๐ผ๐๐ to each execution plan based on factors such as the number of disk reads and the amount of CPU time required to execute the query.
Finally, the optimizer ๐ฐ๐ต๐ผ๐ผ๐๐ฒ๐ ๐๐ต๐ฒ ๐ฒ๐ ๐ฒ๐ฐ๐๐๐ถ๐ผ๐ป ๐ฝ๐น๐ฎ๐ป with the lowest cost as the optimal execution plan for the query. This plan is then used to execute the query.
