SQL tuning is a broad topic and many books have been written as reference.
It’s important to benchmark and profile to simulate and uncover bottlenecks.
- Benchmark – Simulate high-load situations with tools such as ab.
- Profile – Enable tools such as the slow query log to help track performance issues.
Benchmarking and profiling might point you to the following optimizations.
Tighten up the schema
- MySQL dumps to disk in contiguous blocks for fast access.
VARCHARfor fixed-length fields.
CHAReffectively allows for fast, random access, whereas with
VARCHAR, you must find the end of a string before moving onto the next one.
TEXTfor large blocks of text such as blog posts.
TEXTalso allows for boolean searches. Using a
TEXTfield results in storing a pointer on disk that is used to locate the text block.
INTfor larger numbers up to 2^32 or 4 billion.
DECIMALfor currency to avoid floating point representation errors.
- Avoid storing large
BLOBS, store the location of where to get the object instead.
VARCHAR(255)is the largest number of characters that can be counted in an 8 bit number, often maximizing the use of a byte in some RDBMS.
- Set the
NOT NULLconstraint where applicable to improve search performance.
Use good indices
- Columns that you are querying (
JOIN) could be faster with indices.
- Indices are usually represented as self-balancing B-tree that keeps data sorted and allows searches, sequential access, insertions, and deletions in logarithmic time.
- Placing an index can keep the data in memory, requiring more space.
- Writes could also be slower since the index also needs to be updated.
- When loading large amounts of data, it might be faster to disable indices, load the data, then rebuild the indices.
Avoid expensive joins
- Denormalize where performance demands it.
- Break up a table by putting hot spots in a separate table to help keep it in memory.
Tune the query cache
- In some cases, the query cache could lead to performance issues.
Source(s) and further reading: SQL tuning
- Tips for optimizing MySQL queries
- Is there a good reason i see VARCHAR(255) used so often?
- How do null values affect performance?
- Slow query log
We are sorry that this post was not useful for you!
Let us improve this post!
Tell us how we can improve this post?