Do you know how to best design a MySQL database, or how to best structure a MySQL query? If not then this post will certainly interest you.

Here we will be discussing the best MySQL practices you can use not only to keep your database running smoothly and quickly but also to keep it user friendly.

10 – Use sensible field names

Have you ever started on a project and designed the database using ambiguous field names like ‘x’ or ‘uft’ or other silly abbreviations, then it comes to writing a query and you wonder to yourself “What the heck did I call the field for First Names?”. Well this is possibly one of the worst things you can do to hurt the usability of a database. Instead simply call a field ‘FirstName’ or ‘Address1′ and save yourself all the hassle later of looking it up. Save time, name fields by what they contain.

9 – Don’t use ‘SELECT *’

Let’s create a scenario, you have a login form on your website and to check if the details are correct you query the database. This database could contain loads of fields that are totally unnecessary for this certain query like address or phone number. You would only need the username, password and possibly email to check the user input against. So why grab all that unneeded data and use up resources when its totally avoidable. Instead of structuring a query like

SELECT * FROM Users WHERE Username = 'bob'
AND Password = 'password'

Change it so that it only takes what it needs.

SELECT Username AND Password FROM Users WHERE Username = 'bob'
AND Password = 'password'

As you can see from that, it will only get the two fields that you need, reducing the time it takes to run the query. Good for your database server and good for the end user.

8 – Limit queries where possible

Another time and resource saver here. When you know your query will only return one result, is there any point in continuing to search the database after it has been found? That’s what’s happening if you don’t limit, it will search the entire database even after it has found the record you are looking for. A login query will only ever return one result, so let’s add it into the query to stop searching when that result is found.

SELECT FirstName FROM Users WHERE Username = 'bob'
AND Password = 'password' LIMIT 1

Et voila, the query will be ended when the record is found, another time benefit to the end user.

7 – Create an ID field

When you are creating your database, implement an ID field. Make this one of the INT types, primary key and auto increment. This way, every single record will have a unique integer as the identifier, which is much cleaner and faster than a VARCHAR. A VARCHAR as the primary key is much slower than an INT due to processing time, which is why it is not recommended.

6 – Static tables are best

First, do you know what a static table is? Clue’s in the name. A table is considered static when all of the column types have a fixed length. Examples of fixed length types are INT, DATE and DOUBLE, but the ones that are not static (dynamic) are VARCHAR, TEXT and BLOB so these are best avoided. Although a table is only static when all of the columns are fixed length so if you have one VARCHAR then a table instantly stops being static.

5 – Split large queries

If you have any maintenance scripts you run on your database which deletes or inserts a large amount of rows (more than about 1000) then you are better off splitting them up. MySQL works most efficiently when queries finish sooner, so if you split a large delete query into multiple smaller ones, you are less likely to lock up your database. This can be done simply by using LIMIT 1000 at the end of the query. To automate running all of the queries, a PHP infinite loop can be used.

while (1) {
	mysql_query("DELETE From Comments WHERE PostedDate <= '2010-01-01'
		LIMIT 1000");
	if (mysql_affected_rows() == 0) {
		break;
	}
}

The if statement checks if anything was actually deleted, and if it wasn’t then it breaks out of the loop, ending the script. Very simple to do, and can save your database from bringing down your website.

4 – Use smaller columns where possible

In a database, size is everything. A large data size can often cause a bottleneck in your database server, so the best solution to this? Use types that are smaller in size. If you can get away with using SMALLINT or even TINYINT instead of the regular INT then go for it! When large amounts of rows are created, it will make all the difference.

There is a whole document on the MySQL Docs website that explains the sizes and storage uses of the various types and from that we can see that INT uses 4 bytes, where as SMALLINT uses only 2 and TINYINT uses only 1 byte. The space saving opportunities are endless!

3 – Use an index key where necessary

This is possibly one of the best things you can do to decrease the times needed to search a table. Any field that is regularly going to be searched should have an index on it. There is absolutely no reason not to! One query is all that it takes.

ALTER TABLE Users ADD INDEX ('firstName')

And that’s it. The column firstName now has an index on it that will drastically increase the performance of searching by it.

2 – Change the default username and password

If you have a database server like mine, then the default username and password is ‘root’. This is incredibly insecure, and needs to be changed. Again, it can be solved by two simple MySQL queries. First to change the username.

RENAME USER root TO new_username

That changes the username to whatever you specify and this next one will set your password.

SET PASSWORD FOR 'username'@'hostname' = PASSWORD('newpass')

Make sure you update any connection settings in any of your scripts after changing the username and password.

1 – Don’t give users the ability to inject MySQL

The number one rule of MySQL is don’t trust the users. If you have a database incorporated into a PHP script which inserts data, for example a registration form then it is possible for a user to inject SQL. It’s a bit complicated to explain, but there is a handy .pdf over at dev.mysql.com that goes through it in epic detail. All you really need to know is how to defend yourself against it.

The easiest way is to simply sanitise any data that is received from the user. Let’s consider this:

$name = $_POST['name'];
$query = "INSERT INTO Users (name) VALUES ('".$name."')";
mysql_query($query);

Currently the name variable is whatever the user inserts and that could contain SQL to delete all tables. Not good. So we sanitise the string to make that impossible. Here is the new code with the sanitation.

$name = filter_var($_POST['name'], FILTER_SANITIZE_STRING);
$query = "INSERT INTO Users (name) VALUES ('".$name."')";
mysql_query($query);

Notice the change on the first line? Good. That’s SQL injection safeguarded against.

I hope you have learnt from this post, and use at least some of these practices. Any questions? Leave a comment and I will reply.

Ollie Armstrong Software Developer

Ollie works on WordPress plugins, supporting CatN systems and other projects. Still in college, he might be the youngest Zend certified developer in the UK!