MySQL optimizations: indexes

Many 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.