When I started writing this blog post, I had titled it “Tips for Designing Databases” and I planned to talk about various database design techniques. However, as I did more and more research, it dawned on me that one of the most crucial, and most overlooked, components of database development, is the selection of data types for columns.
Much of the information presented in this article was taken from presentations by Jay Pipes and a talk by Ronald Bradford. The talks are The Top 20 Design Tips For MySQL Enterprise Data Architects, Join-fu: The Art of SQL Tuning and SQL Query Tuning: The Legend Of Drunken Query Master.
Now that we know we want to scale our application, we first need to make sure it’s running at peak performance. There are a number of things that we can and must do in order to ensure that the newly scaled application uses resources appropriately, runs efficiently, and, most importantly, does not require excessive resources which will amount to extra costs.
The intuitive will note that many if not most of these suggestions are performance enhancements, not scaling techniques. Why then are they in an series about scaling? Scaling is about more than just adding hardware. It’s also about making sure your system runs better. You can add lots and lots of hardware but you will someday be unable to compensate for bad queries and poor optimization. So before we start adding servers, let’s take a look under the hood.
I recently worked on an application I built some time ago. It was built before I had regard for performance or cared about how a MySQL database should be built. As such, it had no indexes.
That changed, especially when I started playing with old data that I wanted to migrate. The read time on 15 rows in a 325-row table was some 0.86 seconds (which isn’t bad but isn’t great). When indexes were applied, however, the read time went down to 0.01 seconds, an 88% decline in read time.
I’ve given a lot of thought to the issue areas I wish to cover through this blog. And though occasionally I will deviate from these areas to comment on other technological thoughts, questions or ideas, I wanted to give you, the reader, the “why” for the reasons you should be reading this blog.
The six areas of focus that I will be discussing and debating (in no particular order):