MySQL Optimization

Oct 28th, 2011

When it comes to utilizing a database to support your website, it is helpful to understand how your database works with your website, and how to make every database on your website hosting account more efficient. A database is a large collection of data, similar to a warehouse full of goods. How well you organize those goods in the warehouse determines how quickly you can find an individual item when you need it.

Consider your database an auto parts warehouse. If someone came into the warehouse and told you they needed a spark plug, how easy would it be for you to find that spark plug if you had no organization in your warehouse? You would have to walk up and down each aisle looking in each box to see if that box contained spark plugs. This could take forever!

A MySQL database puts your data into tables that are easy to navigate. This would equate to putting your auto parts into specific aisles in your warehouse. Now, you would know which aisle your spark plugs were in, so you would only have to look into each box in one aisle until you found those spark plugs. This is much better than having to go through the entire warehouse, but still could take some time since there may be thousands of items in one table that you still have to sift through to find specific data.

Optimizing your MySQL database is the best, most efficient way to improve the performance of your website. Instead of just connecting your website to a database, and letting your queries wander around your database aimlessly looking for specific data, you can create indexes that guide your queries directly to the proper location of the requested data. Now you can walk directly through that auto parts warehouse to the aisle with the spark plugs, and know exactly where on that aisle the box of spark plugs is.

Indexes are created on a per column basis. If you have a table with the columns: name, address, telephoneNumber, and birthdate and want to create an index to speed up how long it takes to find birthdate values in your queries, then you would need to create an index for birthdate. When you create this index, MySQL will build a lookup index where birthdate specific queries can be run quickly.

It is not necessary to create an index for every column, but any column which is queried frequently could benefit from an index. Creating indexes for your tables is crucial for your database, it allows it to perform faster and more efficiently than it would normally.

If you use well written queries with efficiently indexed tables, you will find that your websites load incredibly fast compared to poorly written queries with no indexes. When you get started working on your website, take the time to analyze each table you create before you launch, just to make sure it is optimized and ready for action.

You may not realize that certain columns are going to be utilized quite as much as you expected, so it is also important that you revisit your databases on a regular basis to determine which tables and columns are working efficiently, and which ones have slowed you down. In our next blog entry, we will discuss MySQL database maintenance in greater depth.