This blog post is a continuation of my previous blog posts mentioned above. In my previous posts, I mentioned Scaling Relational Databases using
In this post, I will mention more about SQL Tuning. 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.
TEXTfor large blocks of text such as blog posts.
TEXTalso allows for boolean searches. Using a
TEXTfield results in storing a pointer on the 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
This is the last post as part of Techniques to scale your Relational Databases series. Hope you enjoyed this 3 part series of blog posts.
Thank you for reading
Hope you find these resources useful. If you like what you read and want to see more about system design, microservices, and other technology-related stuff... You can follow me on
- Twitter here.
- Subscribe to my newsletter above.