Main Content

What a difference a MySQL Index made

Archive - Originally posted on "The Horse's Mouth" - 2009-02-25 06:28:06 - Graham Ellis

If you add an index to a column in a MySQL table, you provide a very quick way of looking up a row or set of rows based on the value in a certain column, but at the expense of additional disc space and a bit more internal management.

Here's an example in which I look up a record (by URL) in a table of around 15000 records, some of which are quite large.

mysql> select url, halflife from cn_content where url = '/net/recents.html';
+-------------------+----------+
| url               | halflife |
+-------------------+----------+
| /net/recents.html |        2 | 
+-------------------+----------+
1 row in set (0.30 sec)

That's running at around 3 requests per second, which as a part of our web site logging is a significant load. Let's create an index, stating that the URL will be unique in the first 100 characters:

mysql> create unique index pagename on cn_content (url(100));
Query OK, 15176 rows affected (1.80 sec)
Records: 15176 Duplicates: 0 Warnings: 0
mysql

This did not work for me first time; the were a couple of lines where the pagename was not unique in my table, and I had the "opportunity" to resolve the issues. Once sorted, my enquiry gave the same results (so it was plug - and - play compatible) but much faster:

mysql> select url, halflife from cn_content where url = '/net/recents.html';
+-------------------+----------+
| url               | halflife |
+-------------------+----------+
| /net/recents.html |        2 | 
+-------------------+----------+
1 row in set (0.02 sec)

That's up from 3 requests per second to 50 - and a huge improvement!