Database optimization is a rather hard topic. Every database is different and may need different methods of tuning. These are some of the most common places to check for improvement.
Creating proper indexes
One of the easiest ways of speeding up your database queries is by creating indexes. Since it’s so easy to create one, they are usually a go-to solution. Although there is a small caveat – you need to know what columns to put into indexes. Since they are so easy to use, you may want to create all possible combinations of indexes – which would be a bad idea. On every query execution, the database will have to analyze which index will be the best to use – and that also takes time.
Optimizing SQL queries
Writing SQL queries is similar to writing functions in your codebase. Usually there are few ways of achieving the same result, but only a handful of them are optimal. If you have slow queries, especially if they are long and complicated, there is a high chance that you can rewrite them in a more optimal way.
Correlated subqueries
Correlated subqueries are a great place to speedup your query execution times. Since they are rerun on every row you should use them only when there is no other option. But how to substitute them? There are a few options:
- rewrite query with regular
JOINs - use
WITHqueries
Since subqueries are so easy to write, they also are easy to use in the wrong place. You should analyze your queries and check if you can rewrite them with JOIN.
If you can’t think of a way to use JOIN in your query and you have access to bare SQL, you can try to use WITH query. It will create a CTE – Common Table Expression – which is a result set of some queries. This result set will be stored just for the time of your query execution.
Retrieve only necessary data
Other way of indirectly speeding up your query is reducing the result set’s size. This will reduce the amount of data that needs to be retrieved, which will decrease execution time. You can achieve that by removing unnecessary columns in queries. Another way of reducing the size of your result set is by implementing pagination.
Diagnostics
One other important part of optimization is checking if all the changes that you’ve made were useful. In most database engines you can set up query logging. You can either log all queries and have a huge amount of data, but most of it will probably be useless. You can set up a logger to only catch queries that exceed some duration. That way you will gather only slow queries.
After you gather all this data you can use your database to check execution plans of each query. It should allow you to determine why your queries are slow and give you an idea how you can speed them up.