MySQL Performance Optimization

Users want to see pages on a website load quickly. Having SQL queries run faster will help retrieve the necessary data quicker and diminish overall page load times. As a website becomes popular, more and more pages are requested and need to be loaded at the same time. Since server resources are limited, it can accommodate more requests for data if SQL queries are optimized.

Web developers who work on database-driven websites should really think about how their SQL queries impact overall page load times. When one develops a database-driven website, one can implement SQL optimizations while initially writing the SQL selection logic and not near the end of a project’s lifecycle. One just has to keep in mind how much data is really necessary for the specific page. When a page is loading slowly and is retrieving a lot of data, it is a good candidate for SQL optimization.

One way to optimize SQL queries is to only retrieve the data that you need to display on the page. Many early development SQL queries may look like:

SELECT * FROM user WHERE id = 5;

In this simple example query, we want to retrieve all data columns from the database table “user” but only for the user designated with the id of 5. Many queries start out like this in the early development phases because it is usually unclear which specific columns need to be retrieved. This would be fine if we really needed and planned to use all of the data in that row.

But what if the page that uses this SQL query only displays the user’s name? There could be a dozen columns or more on the “user” table, most of which aren’t being used. So to speed things up, the SQL query could be changed to something like:

SELECT first_name, last_name FROM user WHERE id = 5;

When this type of SQL query is executed on a real, production website with a “user” table that has over 50 columns and more than 6,500 rows, the first SQL query ran for 0.0801 seconds while the second SQL query ran for 0.0249 seconds. That’s over 3 times faster for this one query alone!