HostMonster 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

OPTIMIZE TABLE tablename

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 63,038 views bookmark tags: cpu exceeded index indexes mysql optimize optimizing queries query (updated 961 days ago)


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 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 (updated 960 days ago)
Knowledgebase Article 59,350 views tags: cpu exceeded index indexes mysql optimize optimizing queries query

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 (updated 569 days ago)
Knowledgebase Article 82,456 views tags: indexes mysql optimize optimizing queries query

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 (updated 960 days ago)
Knowledgebase Article 58,470 views tags: indexes mysql optimize optimizing queries query

Related Help Content

Why does my site get throttled, or why did my account get suspended for performance? (updated 38 days ago)
Knowledgebase Article 127,423 views tags: cpu database mysql optimize query script slow throttling

How to repair and optimize your database using phpMyAdmin: (updated 647 days ago)
Knowledgebase Article 110,919 views tags: database mysql optimizing repairing slow speed

A guide on performing a standard SQL query using the phpMyAdmin interface (updated 456 days ago)
Knowledgebase Article 5,887 views tags: databases mysql phpmyadmin

Tips and tricks to make your wordpress blog load faster (updated 429 days ago)
Knowledgebase Article 16,425 views tags: optimizing wordpress

How can I optimize my images? (updated 1655 days ago)
Knowledgebase Article 66,893 views tags: images optimize

This article will explain MySQL database and user creation and deletion, and explain how to assign a user to a database. (updated 442 days ago)
Knowledgebase Article 309,458 views tags: creation database mysql

How do I make my MySQL database compatible with the version you provide? (updated 1652 days ago)
Knowledgebase Article 55,308 views tags: database mysql

This article will show a few MySQL errors along with explanations as to why they might be occurring. (updated 445 days ago)
Knowledgebase Article 6,475 views tags: errors mysql