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.
- 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.
- 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.
- Denormalize where performance demands it.
- Break up a table by putting hot spots in a separate table to help keep it in memory.
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.
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