MariaDB / MySQL Performance Tuning and Optimization: Best Practices for Writing High Performance SQL Queries
Our MySQL consultants get asked this a lot when undertaking MySQL performance tuning. So, we have decided to put together a list of best practices for writing high performance SQL queries. While database implementations differ, the underlying mathematics they are based around apply to all of them. Consequently these best practices broadly apply across most database management systems. In this article we discuss a list of best practices to help you write queries that perform well and avoid writing queries that end up being unoptimizable.
- Avoid using sub-SELECTs when you can use JOINs.
- If you have to use a sub-SELECT, consider using a temporary table instead, insert the rows the sub-select would return into it, create appropriate foreign key / search key indexes, and join against the temporary table. It will usually be faster than a sub-SELECT.
- If you have to use a dependent sub-SELECT, consider using a common table expression (CTE) instead. It can enable you to take better advantage of early-stopping possibilities arising from use of LIMIT clauses.
- Avoid using VIEWs. They often end up having to be resolved by storing their intermediate output in a temporary table.
- If you have to use VIEWs, consider creating materialized views. If you need a materialized view to be up to date in real-time, write triggers on the underlying tables to keep the materialized view up to date.
- Avoid using OR matches in your WHERE clause, as they will typically result in queries being unable to use indexes optimally. Instead, break up the query into a UNION of queries with each query matching one of the elements being OR-ed together. This will enable you to make sure that each of those queries can run optimally indexed.
- DELETEs and UPDATEs can be slow and cause a lot of unnecessary row locking. They lock every row examined, rather than every row modified. Make UPDATEs and DELETEs them work smoothly and unobtrusively by making them run by primary key.