How to Speed Up SQL Queries

Today, most websites and apps are heavily dependent on SQL queries to read and write data. But over time, your database size increases, developers tend to add more new queries, the existing queries evolve to become more complex and number of concurrent users running these queries also increases. As a result, the performance of your SQL queries may slow down significantly. This will hurt your website/app’s user experience and drive away users. In this article, we will learn how to analyze your SQL queries for performance problems and speed up SQL queries.

Why Speed Up SQL Queries

There are several reasons why you need to optimize your SQL queries.

1. Database size increases – Over time, as database size increases, the SQL queries tend to run slower due to longer search times and time taken to iterate over table rows. So it is important to speed up SQL queries as your data begins to grow.

2. More queries to be executed – As your website/app grow more complex, more and more SQL queries are added to support new features. So your database server has to process more queries per user. This can slow down your database queries.

3. Existing queries become complex – As your website/app evolves to deliver new features and upgrade existing features, the existing SQL queries to implement them become more complex and time consuming. In such cases, you need to optimize them.

4. More concurrent users – Over time, the number of people using your website/app will obviously increase, and more concurrent users will be running SQL queries on your database. This will stretch your database resources and slow down your SQL queries.

5. For Business Intelligence – Data reporting and analytics systems need the SQL queries to run quickly so the result can be quickly visualized.

Analyze & Diagnose

Here are some of the steps you can take to identify bottlenecks.

1. Identify Long Running Queries – First look at the execution time of all queries and identify the top 10-15 longest running queries hitting your database. This will point you in the right direction.

2. Use EXPLAIN – Use EXPLAIN or EXPLAIN ANALYZE commands to analyze the query execution plans of these long running queries, without actually executing them. It helps you understand performance bottlenecks such as inefficient joins, full table scan, etc. It also gives an estimated cost of each operation for a given number of rows, allowing you to compare different query plans. This will help you find out the factors increasing execution time in long running queries.

3. Monitor Resource Usage – Next, check the resource usage (such as CPU usage, memory usage & I/O) for long running queries. Also, look at the number of rows processed. You can use third party tools like MySQL Query Analyzer, pg_stat_statements for this purpose.

How to Speed Up SQL Queries

Here are some techniques to speed up SQL queries.

1. Avoid SELECT * – One of the simplest ways to optimize SQL queries is to avoid using “SELECT *” in SQL queries and instead, explicitly specify the actual column names you want in your query result. When you use SELECT * in your SQL queries, it will retrieve all columns in result and increase the amount of data transferred and processed.

2. Use Indexes – One of the most common and effective ways to improve query speed is to index those columns that are used in WHERE, JOIN, GROUP BY, and ORDER BY clauses so that database can easily find their data without having to scan the entire table.

3. Use JOINs efficiently – You will have to invariably use JOINs in your SQL queries, when you need to get rows data from multiple tables. While doing so, use inner joins as much as possible, since they are more efficient. Also, before you write join, go through your database schema once and see if you can retrieve the required column using some other table, without using the join.

4. Filter Results Early – Whenever possible, use WHERE clause and LIMIT clauses to reduce the number of rows fetched in result. It also helps with pagination of result

5. Avoid Functions & Calculations in WHERE clause – If possible, avoid using functions in WHERE clause of your SQL queries. When you use functions, it prevents the database from using INDEXES and forces a full table scan, which is time consuming. For example, instead of using WHERE year(date_joined)=’2026′, use something like date_joined>’2026-01-01′ and date_joined<‘2027-01-01’.

6. Use UNION ALL instead of UNION – When you use UNION statement, it will remove duplicates and therefore need to sort the involved tables. On the other hand, UNION ALL statement does keeps duplicates and does not sort the tables so it is faster.

7. Use EXISTS instead of IN – Both EXISTS and IN operators are used to check if a row is present in a sub query. EXISTS operator stops searching when a match is found whereas IN operator will continue to search even after a match is found. So EXISTS is more efficient than IN statement and should be used as much as possible.

8. Avoid Wildcards in LIKE clause – SQL developers use LIKE operator to do pattern matching in columns. However, it is advisable to avoid using wildcard characters in LIKE operator, since it prevents database from using indexes.

Database Infrastructure Optimization

Here are some infrastructure optimization you can implement to speed up SQL queries.

1. Update Database Statistics – In every database, the query optimizer refers to database statistics to come up with efficient query execution plans. These statistics are automatically updated by database server in most cases. However, if you have made a large number of changes in a short span of time, then sometimes, the database statistics may not get updated. In such cases, it is better to manually update database statistics so that the query optimizer has the latest information to create query execution plans.

2. Stored Procedures & Parameterized Queries – Stored procedures and parameterized queries are pre-compiled with execution plans that can be re-used. This reduces query compilation time. So it is advisable to use stored procedures and parameterized queries as much as you can.

3. Caching – If you need to frequently run some static queries, whose results do not change frequently, then you can cache the results of such queries, using caching tools like Redis. Thereafter, when you run such queries, the database server will return the result directly from the cache instead of processing it from scratch. This reduces database server load.

Conclusion

In this article, we have learnt different ways to analyze SQL query performance, identify performance bottlenecks and optimize SQL query to improve speed. It is advisable to use the above mentioned steps one by one and see which one works for you.

Also read:
How to Get Row Number in PostgreSQL
How to Delete Duplicate Rows in MySQL
How to Get Multiple Counts in Single SQL Query