MySql Best Practices

MySql Best Practices

June 24, 2014

So, onto our next Tutorial Tuesday post. For the database monsters amongst you, some of these might be a little simple, but these are some of the common mistakes we find when picking up websites. It goes without question that you should optimise a website from top to bottom, and below are a few of the things we focus on when it comes to database time.

Use an Object Relational Mapper (ORM)

ORM's are great, they can help cut down on the raw sql you need to write and can provide certain performance benefits. We use Doctrine here with Symfony. They're great for 'lazy loading' which means it will fetch values as and when you require them in the front end, but be very careful. In doing this, they can start building up a deluge of smaller queries which in themselves become a drain on performance. As ever, don't miss out on that join opportunity.

Avoid SELECT *

As with images, CSS and javascript, we want to keep the data that is transferred to a minimum, and so to with the data returned from the database. If you are joining several tables together and then returning all of the data, but only require one column, the rest is simply fat that needs to be trimmed. Select only the columns that you require.

When fetching a unique row: LIMIT 1

We're trying to stop the database from doing any more work than it has to, and this is a great one for keeping it slim. If you know you are only looking for one snippet of information, for example whether you have any blog posts attributed to a particular category, you only need to find the first instance of this. Adding LIMIT 1 will tell the engine to stop searching through the entire table or index and returning all of these results.

Don't use ORDER BY RAND()

Seems like a good idea when you first start out, but as the data increases in the database, so too do the problems that the rand function causes.

There are far better ways of getting a random record out of the database. One example we use quite a lot is to select a count of the rows in the database and use this as an offset for returning the results.


// This is bad:
$a = mysql_query("SELECT title, content FROM blog ORDER BY RAND() LIMIT 1");

// This is good:
$a = mysql_query("SELECT count(*) FROM blog");
$b = mysql_fetch_row($a);
$rand = mt_rand(0, $b[0] - 1);
 
$a = mysql_query("SELECT title, content FROM blog LIMIT $rand, 1");

Index search fields

If there is a particular field you know you are going to perform searches on that isn't already a primary or foreign key, index it. The database will use this index information to locate the row you are looking for, without trawling through the entire table.

Use an id field (nearly always)

For (nearly) every table that you use in your database, have an id field that is the primary key, auto increment, and some form of int. It might be that you have a varchar title field on the blog that is unique, but varchar are slower to compute than int fields, and you can't go wrong with having an AUTO_INCREMENT INT being unique. It also means that the information within the row then becomes far more flexible.

The nearly within this comes from relational tables, in a many-to-many for example, where a row is created with the primary keys from two other tables.

Other Recent Articles
September 23, 2014

Now that Apple's desktop OS is available in beta to everyone, there are bound to be a lot of people upgrading between now and the official release date of 21 October (unconfirmed). Having updated in the office to the latest beta, we found one of our most used bits of kit, Homebrew, was not working. But fear not, it is a simple change!

July 10, 2014

This is a pretty open ended question but there are a few factors from both sides of the partnership that affect how much a website will cost you. Whether you choose to proceed with a site builder, or want something unique and more substantial, the costs have various pros and cons.