Servage Magazine

The database performance is a common bottleneck for websites. This is rarely due to the actual database server, but most often caused by misuse of the database connection by making overly complicated requests, forgetting to optimize indexes or field types, forgetting caching and other reasons. Fortunately it is quite simple to implement a wide range of improvements which really can make an overall difference for the user-perceived performance of your site.

Let us say you have a site which on average uses 2 seconds to render in the browser. Analyzing the different parts of that load time, you could have an allocation similar to the following:

• Request to server: 200ms
• Webserver script execution: 1.000ms (a second!)
• Database queries: 500ms (included in the script execution)
• Response to client: 200ms
• Browser rendering: 300ms
• Total experienced load time: 2.000ms (two seconds is acceptable, but not impressive)

The example illustrates that database queries may take up a large part of the load time, and therefore present a good place to improve performance . However, the example also shows that database improvements alone cannot make a response feel instant. It is furthermore important that you analyze your responses and identify the most obvious bottlenecks. It pays of to start optimizing there. Other common bottlenecks include large stylesheets, large Javascript files and slow execution thereof, or generally too many requests per page.

Below you find some optimization topics for MySQL databases.

Indexes

The database can store some field values for quick searching. It enables the database to find results faster when looking through certain columns. This process is called indexing. For example if you have a table for users with an indexed email value and a non-indexed first name value: Results based on a where-condition on the email would return faster than results based on a search for the first name. Note that an index is automatically applied to the primary key (usually called id), and therefore lookups based on the id are mostly already fast.

You can even combine multiple fields in indexes, which makes great sense if you often combine multiple fields in your search conditions. So if you often search on email or first name, you would have those columns indexed individually. However, if you also often search on a combination of two fields like user type and user status, then you could create an index holding both fields.

Data types

Every time you add a new column to a table in the database, you have to decide upon a data type. Some types like varchar, integer or datetime have become more popular than others (probably because they are used over and over again in various tutorials). However, there are plenty more datatypes, and choosing the right one has a great impact. Some fields also allow you to set a specific length for a field, but from experience that setting has less impact for smaller sites (but is not totally irrelevant).

It is also important to distinguish between dynamic and fixed length fields. For example a char field is fixed in length, and will always consume the same space in the database storage. A varchar on the other hand is variable in length, thus only consuming the required space. However, the fixed length has the benefit of the database server better knowing blocks of data, thus making it faster to select data – at the expense of storage consumption.

CHAR(255)

VARCHAR(255)

Both data types above can have the same max length, but the char is always stored as 255, even if the string is shorter.

Redundant conditions

Some queries contain many ‘where’ conditions, and while they seem logic at the time of programming, they are usually not always needed. It depends on your application logic. Therefore evaluate your queries for such redundant conditions, because they are just unnecessary checks that slow down the database. For example if you are searching for pregnant women in a table of people, you don’t really need to check the person’s gender, as pregnant people are exclusively women (Not dealing with medically special cases).

SELECT name FROM people WHERE gender = 'female' AND pregnant = 'yes';

SELECT name FROM people WHERE pregnant = 'yes';

The gender status makes sense in a way, but is redundant if the logic is true, that only women can be pregnant. Removing the second condition would make the query faster – especially if the gender column was not even indexed.

Joins

When making complex queries you are likely to use join statements to fetch the correct data. However, those can be a very big factor in slowing down queries and should therefore be well considered, and possibly avoided.

Database performance improvements are a complicated thing to deal with. However, implementing some of the principles above should already get you going in a right direction. There is no correct way, because there are pros and cons to most of the suggestions above. Therefore you need to find your own bottlenecks, and solve them with custom solutions specifically for your system.