The impact of indexes on query design
As we know, indexes improve the read performance of queries, so we should always keep in mind the columns that participate in the JOIN, WHERE, GROUP BY, HAVING, and ORDER BY clauses must have an index. This is because columns participating in the JOIN and WHERE clauses are the deciding factor and are read/accessed first from the raw data. Then all other filters are applied, such as GROUP BY, HAVING, and ORDER BY, based on the result set and the functions used in the queries.
If you write a query that has an index on the column being used in the WHERE clause for filtering the data, its performance will be drastically better after we run the same query after creating the index on that column. This applies to the columns participating in the JOIN, GROUP BY, HAVING, and ORDER BY clauses.
To further improve performance, you should include the columns participating in the SELECT statement, and if you are going to create a report for a particular...
