MySQL optimizations: indexes

January 20th, 2009 by

MySQL LogoMany of us use MySQL to power our websites. Using a database is necessary for many common applications like WordPress, Joomla, and phpBB due to the amount of information which they serve. A database backend is a natural choice for a website which becomes more complex as it allows for a more efficient storage and organization of data as well as an easy way to retrieve and serve content.

Information inside a typical MySQL database is stored in tables. Information is retrieved by querying these tables, usually with some criteria so that we receive only the information which we want. In order to fulfill a query and return the right information, MySQL must examine all rows in the tables and see which one’s match our criteria. This can be a resource intensive process which can slow down a website’s performance depending on the amount of data which must be examined.

MySQL has a great feature which can help speed up queries. Enter table indexes.

An index for a MySQL table is similar to the index of a book in that it’s a quick way to find the information we’re looking for. Instead of reading through an entire table to find the information we want, MySQL can consult the table index which greatly increases the speed of the search. Pretend we have this table named eyeColor:

mysql> describe eyeColor;
| name      | color   |
| Morgan    | Hazel   |
| David     | Hazel   |
| Paul      | Blue    |
| Cameron   | Green   |
| Matt      | Brown   |

I would like to query this table to see who has blue eyes. My query would look something like this:

mysql> select name from eyeColor where color = 'blue';

In this query, “color = ‘blue’ ” is my criteria for the search. I only want to see who has blue eyes and this limits the information which is returned to those who only have blue eyes. I can gain some information about this query by asking MySQL to explain it:

mysql> explain select name from eyeColor where color = 'Blue';

Click to Enlarge

See how there are NULL values for many of the above fields? This looks like a simple query in the table eyeColor. The type is ALL, meaning that a full table scan is performed. This is the slowest type. The possible_keys and key values are NULL, meaning no indexes could be or were used. According to the rows information, 5 rows had to be examined to fulfill this query.

Now I’m going to create an index using the first 3 characters of the items in the color column:

mysql> create index index_color on eyeColor( color(3) );

This time, when I ask MySQL to describe my query we should see that things have changed:

mysql> explain select name from eyeColor where color = 'Blue';

Click to Enlarge

Again, this is a simple query in the eyeColor table. This time the type is ref, indicating that an index will be referenced. The possible_keys and key values show that my index index_color was used in this query. According to the rows information, only 1 row had to be examined to fulfill this query.

This shows that without an index, every row in the table must be examined to fulfill a query. Some sites have tens of thousands to several million entries in a table. You can see that without an index that there is a lot of work which must be done. Major website softwares like WordPress, Joomla, and phpBB create indexes in their databases. If you design your own site using MySQL then you may wish to start using indexes to increase the efficiency and speed of your site.

Have some more ideas on optimizing MySQL to speed up your Web site, please share.

  • Share this post

Dynamic Websites with jQuery and extJS

Shared Calendars with Google and/or CalDAV


Brian Cressall
# 21st January, 2009

Indexes are great optimization tools. One caveat is that there is a performance cost to maintain an index. It is typically not a huge cost, but it still exists. When you insert or delete a record, the index must be updated. When you update a record, an index may have to be updated if your update affects the index.

Some people think that indexes are a magic bullet and will index every column in their table. While that can help you search on anything, it causes a performance hit and is usually unnecessary. The rule of thumb is to only index fields that you search on.

In a future article, maybe we could talk about composite indexes and how the order of the indexed fields is important. Many people get confused on how indexes work and will create single-field indexes instead of a more appropriate composite index.

# 4th February, 2009

Great article thanks

# 29th December, 2009

I am installing a java based media browser app [hopefully if I can debug it] on my site here at westhost.
As part of it’s abilities it uses the yahoo searchAPi to pull a directory of videos.

Yahoo leads me to believe that with a properly constructed rss feed in xml form and parked in say my videos directory, that the yahoo search engine will come crawl it, and then the urls for my videos will be available through the java based media browser. Thus releiveing me of putting all 800+ videos into the MySQL database. Which I won’t mind doing but I want to do this rss feed thing with yahoo first.

anyone got any input on what the format of the xml rss feed file needs to look like?

Like this maybe:

(xml header)

and so on?