SQL tuning

SQL tuning for a faster database

If you’re running MySQL or MS SQL databases, you’re probably familiar with the basics of the SQL language. While SQL is relatively easy to pick up, there are always opportunities when it comes to optimising your database to run as quickly and efficiently as possible.

Minor tweaks can often have major effects on database performance, especially when dealing with large volumes of data. By implementing some fairly straightforward SQL tips, you can trim the fat to ensure your database is as fast as it can be, and deliver the best-possible experience to users.

Improve SQL performance, even if you can’t change the backend

On certain platforms, like shared hosting, you won’t be able to change the backend configuration. Even if you do have access to the backend, it’s not always practical to play around with your setup. The good news is that there are still plenty of ways to boost database performance by making your SQL queries as efficient as possible.

Essentially, you want to ensure the database only retrieves the data it actually needs for a given query. SQL statements dictate what is or isn’t retrieved, and there are various ways to write an SQL statement to deliver the same result. However, optimised SQL statements will provide increased performance, because the database won’t take the time to retrieve any records that are irrelevant to the query.

Optimising SELECT statements

SELECT statements can be optimised by specifying the columns of the data you want to be returned, rather than just ‘*’. For example, for a query that requires a home address from a table that contains complete personal profile data (personal_info), you could write:

SELECT * FROM personal_info;

This will retrieve data from all the columns in the table, whether or not they contain home address records. If it’s a large table, you can expect a significant performance hit as the database trawls through the whole thing. But if you write it like this:

SELECT street_address, town_city, county, postcode FROM personal_info;

Now the query is optimised to retrieve only the data that matters to the user, saving processing power and time.

You can also use the LIMIT clause to ensure only data from a certain number of rows is retrieved from a table. For instance, if you only need the first five records from a table with thousands of rows, instead of writing:

SELECT title, author, price FROM books;

You could write:

SELECT title, author, price FROM books LIMIT 5;

Now the database only needs to retrieve data from the first five rows of the table, skipping the rest for maximum efficiency.

These are just a couple of basic examples to illustrate how simple it can be to optimise SQL queries. SELECT statements are a priority for performance tuning since they carry out all database lookup operations, but there are many more possibilities. For further details, you can find a wide range of official MySQL documentation.

Advanced database optimisation

If you’re in complete control of your database configuration, you have a wide variety of SQL tuning options. You can create indexes and primary keys, tweak your parameters and choose the most efficient storage engine for your applications, but these considerations will vary wildly between use cases.

To get a better idea of how you can boost database performance, there are useful tools freely available online. One of these is MySQLTuner which runs a report on your MySQL database, identifying room for improvement and recommending actions you might have overlooked.

Aside from these optimisation techniques, there are various other ways to get the most from your SQL databases. With high-performance web hosting like the Fasthosts Cluster platform, you can get all the advantages of low-contention databases on smart SSD storage. Our powerful dedicated servers offer more features for advanced users, while CloudNX gives you the flexibility to select the right amount of CPUs, RAM and storage to always meet the demands of your database applications.

Be Sociable, Share!