Bluehost Web Hosting Help

Optimizing MySQL: Queries and Indexes Article 3 of 4

Optimizing MySQL: Queries and Indexes Article 3 of 4

Courtesy of: Ian Gilfillan

Ordering by surname is a common requirement, so it would make sense to create an index on surname. But in this example our employee table consists of thousands of people from Swaziland, and with the surname "Dlamini". So we need to index on firstname as well. The good news is that MySQL uses leftmost prefixing, which means that a multi-field index A,B,C will also be used to search not only for a,b,c combinations, but also A,B as well as just A.

In our example, this means that an index of the type

ALTER TABLE employee ADD INDEX(surname,firstname);

is used for a queries such as

EXPLAIN SELECT overtime_rate FROM employee WHERE surname='Madida';

as well as

EXPLAIN SELECT overtime_rate FROM employee WHERE surname='Madida' andvfirstname="Mpho";

which both result in

            |table   | type | possible_keys     | key     | key_len | ref   | rows |Extra      |
            |employee| ref  | surname,surname_2 | surname |      41 | const |    1 |where used |

However, the query

EXPLAIN SELECT overtime_rate FROM employee WHERE firstname='Mpho';

does not use an index, as firstname is not available from the left of the index, as shown below.

            | table    | type | possible_keys | key  | key_len | ref  | rows | Extra      |
            | employee | ALL  | NULL          | NULL |    NULL | NULL |    3 | where used |

If you needed this kind of query, you would have to add a separate index on firstname.

The Query Optimizer, OPTIMIZE and ANALYZE

The magic inside MySQL that decides which keys, if any, to use to in the query, is called the query optimizer. It takes a quick glance at the index to see which indexes are the best to use. Compare it to searching for a CD by the artist "Savuka", called "Third World Child", where there are 2 indexes, one alphabetical by artist name, and the other by album name. At a glance, you see that there are 20000 unique artists, and 400000 unique albums, so you decide to search by artist. But if you knew that there were 50 Savuka albums, and that Third World child is the only album starting with "T", your search criteria would change. You can provide similar information for the Optimizer by running

ANALYZE TABLE tablename;

This stores the key distribution for the table (running ANALYZE is equivalent to running myisamchk -a or myismachk --analyze).

Many deletes and updates leave gaps in the table (especially when you're using varchar, or in particular text/blob fields). This means there are more unnecessary disk I/O's, as the head needs to skip over these gaps when reading. Running


solves this problem. Both of these statements should be run fairly frequently in any well looked after system.

Another factor that most people don't use when indexing is to take advantage of short indexes. You don't have to index on the entire field. Our surname and firstname fields are 40 characters each. That means the index we created above is 80 characters. Inserts to this table then also have to write an additional 80 characters, and selects have 80 character blocks to maneuvre around (disk I/O is the primary hardware bottleneck, but that's for another day!). Try reducing the size of your index - in the example above, rather use.

ALTER TABLE employee ADD INDEX(surname(20),firstname(20));

Now our updates write to an index half the size, and selects have a smaller index to search. Both will be faster (unless you make the indexes too short - imagine a book index, instead of giving the full word, only contained the first letter of the word!. You'd spend a lot of time looking up "semaphore" and "saxophone" when you actually wanted "SQL". Don't do the same to MySQL!

The same applies to the original field definitions. In these days of ample disk space, we don't often worry about space. But smaller usually means faster, so defining our surname and firstname fields as CHAR (255) would be a mistake if the biggest firstname is never more than 20 characters! You don't want to cut names off, but remember that you can ALTER the field later if conditions change, and you need to allow for more characters. I also suggest using VARCHAR rather than CHAR (variable length characters rather than fixed length characters), even though many don't recommend this as they are more subject to fragmentation. I overcome this by using OPTIMIZE often.

Knowledgebase Article 189,334 views bookmark tags: cpu exceeded index indexes mysql optimize optimizing queries query

Was this resource helpful?

Did this resolve your issue?

Please add any other comments or suggestions about this content:

Recommended Help Content

Optimizing MySQL: Queries and Indexes Article 4 of 4

Optimizing MySQL: Queries and Indexes Article 4 of 4 Courtesy of:Ian Gilfillan Most systems need to be highly optimized for selects - take a news site which performs millions of queries per day, but w

Optimizing MySQL: Queries and Indexes Article 1 of 4

Optimizing MySQL: Queries and Indexes Article 1 of 4 Courtesy of: Ian Gilfillan Badly defined or non-existent indexes are one of the primary reasons for poor performance, understanding and then fixing

Optimizing MySQL: Queries and Indexes Article 2 of 4

Optimizing MySQL: Queries and Indexes Article 2 of 4 Courtesy of: Ian Gilfillan Some knowledge of how indexes work allows you to use them more efficiently. Firstly, note that when you update a table w

Related Help Content

CPU Performance Issues

What is CPU Protection

MySQL Repair and Optimize Tables in phpMyAdmin

How to repair and optimize your database using phpMyAdmin:

How To Run SQL Queries in PHPMyAdmin

A guide on performing a standard SQL query using the phpMyAdmin interface

Upgrade CPU and Memory on CloudSites

This article explains how to add CPU and memory upgrades to a CloudSites account.

Optimizing WordPress

Tips and tricks to make your WordPress blog load faster

How to Create and Delete MySQL Databases and Users

This article will explain MySQL database and user creation and deletion, and explain how to assign a user to a database.

Optimizing Your Images

How can I optimize my images?

MySQL Database Compatibility

How do I make my MySQL database compatible with the version you provide?