The road to using Mysql

It's often said that poor use of indexes (or indeed a missing index) is the fastest way to kill a system. An index works by providing a pre-sorted list that the query can access, instead of having to perform a large sort in memory. It must be remembered that indexes come at a cost though, mainly in using up disk storage space (and also in increasing INSERT and UPDATE times). One important thing to note when creating an index, is that it's preferable to minimize the size of indexed fields. An index on a small int field is far better than an index say, on a varchar(100) field. If it's necessary to index a varchar field, consider limiting the index to the first x places, for example:

Code:

The index generally works on a direct WHERE condition for example where colA = 123, or when looking for a min(field) or max(field) value. The index can also be used for LIKE comparisons, so long as the comparison string does not start with a wildcard. So, using "WHERE fielda LIKE 'FARSC%'" will use an index on that field, the following won't: "WHERE fielda LIKE '%FARSCAPE%'". To read up more on how Mysql uses indexes:

mysql manual

Another factor to consider in putting an index on a field, is that of selectivity. If there are too many data records of the same value (eg if the field records gender, 'M' or 'F') then Mysql will find it quicker to load all the records into memory and scan through them, instead of using the index. The Mysql database keeps statistics on table indexes so it can make an informed guess as to when to use an index or not. So, it only makes sense to use indexes where the field has a high selectivty ie the data set contains mostly or entirely unique values.

One thing to remember in creating an index is it doesn't have to restricted to just one column. For example, if you query often on two columns firstname and lastname, you can create a multi-column (or composite) index:

Code:

When you use a composite index you have to remember that the index is only used on the second field mentioned, when the WHERE clause includes the first field. It may be necessary in some cases to create a further separate index on that second field, when you know queries will only specify that second field in the WHERE clause. Also, if you use OR (ie WHERE lastname = 'SMITH' OR firstname = 'PETE') then the index is ignored. You can read more about multiple indexes here:

mysql manual on multiple indexes

Sometimes an index is not used in a query because a function or conversion is carried out on the field.
If you can isolate an indexed field on the left side of a WHERE condition, you can then persuade Mysql to use the index in the query. The best example of this is something I found in the Book Pro Mysql (Kruckenberg and Pipes):

Bad performance:

Code:

Good performance (the index on order_created field will be used):

Code:

There will be cases however where you can't perform a search on a field without using a function or conversion of that field. In these cases, if the query is needed often, it may be more efficient to create an additional column containing the calculated value of that column (always assuming that the extra storage space isn't an issue). For example (again from the ProMysql book), the following SQL will not use an index, even if one exists on the email_address column:

Code:

The workaround for this is to create an additional column containing the reverse of the email address, and then index that column. We can then utilise the index by means of the following query:

Code:

Of course, as well as the additional disk space required for the additional column and index, we also need to ensure that the email_address_reversed field is populated regularly. This could be via the use of triggers on updates and inserts. Or you could run a table update routine at regular intervals. It all depends on how often you need the query to run efficiently.

There's an important consideration I'm only going to touch upon in this article. Sometimes you will need to persuade mysql to use the right index. Although it will normally guess correctly via looking at the query and analysing statistics, there are cases where it chooses the inefficient option. This subject is too large to include in this article, but I can recommend you read the following link showing 7 ways to convince mysql to use the right index

7-ways-to-convince-mysql-to-use-the-right-index

One last thing that may make your queries run faster. If your table has had a lot of deletes, inserts and updates, it may well be worth using the command OPTIMIZE TABLE, for example

Code:

This will effectively de-fragment a mysql database ie it will reduce the size of the table on disk and reduce query response time. It also keeps the index statistics current which helps Mysql decide when to use a particular index on a column.

Well, I feel I've shown a lot of different ways in how to optimize your use of indexes in Mysql. Please feel free to leave comments if you find this information of use (or where you disagree!).

Until next time - Happy coding!