HostMonster Web Hosting Help

Optimizing MySQL: Queries and Indexes Article 1 of 4

Summary

This series of four articles will demonstrate how to optimize your MySQL Databases. These articles have been provided courtesy of Ian Gilfillan

Optimizing MySQL: Queries and Indexes Article 1 of 4

Badly defined or non-existent indexes are one of the primary reasons for poor performance, understanding and then fixing these can often lead to phenomenal improvements.

Consider this example:

 CREATE TABLE employee (
 employee_number char(10) NOT NULL,
 firstname varchar(40),
 surname varchar(40),
 address text,
 tel_no varchar(25),
 salary int(11),
 overtime_rate int(10) NOT NULL
);
To find employee Fred Jone's salary(employee number 101832), you run:
SELECT salary FROM employee WHERE employee_number = '101832';

MySQL has no clue where to find this record. It doesn't even know that if it does find one matching, that there will not be another matching one, so it has to look through the entire table, potentially thousands of records, to find Fred's details.

An index is a separate file that is sorted, and contains only the field/s you're interested in sorting on. If you create an index on employee_number, MySQL can find the corresponding record very quickly (Indexes work in very similar ways to an index in a book. Imagine paging through a technical book (or more often, an scrambled pile of notes!) looking for the topic "Optimizing MySQL". An index saves you an immense amount of time!

Before we repair the table structure above, let me tell you about a most important little secret for anyone serious about optimizing their queries:

EXPLAIN is the keyword in mysql that will help you figure out what is wrong!

EXPLAIN shows (explains!) how your queries are being used. By putting it before a SELECT, you can see whether indexes are being used properly, and what kind of join is being performed...

For example:

EXPLAIN SELECT employee_number,firstname,surname FROM employee WHERE employee_number = '10875';

        +----------+------+---------------+------+---------+------+------+------------+
        | table    | type | possible_keys | key  | key_len | ref  | rows | Extra      |
        +----------+------+---------------+------+---------+------+------+------------+
        | employee | ALL  | NULL          | NULL |    NULL | NULL |    2 | where used |
        +----------+------+---------------+------+---------+------+------+------------+
So what are all these things?
    • table shows us which table the output is about (for when you join many tables in the query)
    • type is an important one - it tells us which type of join is being used. From best to worst the types are: system, const, eq_ref, ref, range, index, all
    • possible_keys Shows which possible indexes apply to this table
    • key And which one is actually used
    • key_len give us the length of the key used. The shorter that better.
    • ref Tells us which column, or a constant, is used
    • rows Number of rows mysql believes it must examine to get the data
    • extra Extra info - the bad ones to see here are "using temporary" and "using filesort"

Looks like our query is a shocker, the worst of the worst! There are no possible keys to use, so MySQL has to go through all the records (only 2 in this example, but imagine a really large table with millions of records).

Now lets add the index we talked about earlier.

If we re-run the EXPLAIN, we get:

+----------+-------+---------------+---------+---------+-------+------+-------+
| table | type | possible_keys | key | key_len | ref | rows | Extra |
+----------+-------+---------------+---------+---------+-------+------+-------+
| employee | const | PRIMARY | PRIMARY | 10 | const | 1 | |
+----------+-------+---------------+---------+---------+-------+------+-------+

The query above is a good one (it almost falls into the category of "couldn't be better"). The type of "join" (not really a join in the case of this simple query) is "const", which means that the table has only one matching row. The primary key is being used to find this particular record, and the number of rows MySQL thinks it needs to examine to find this record is 1. All of which means MySQL could have run this query thousands of times in the time it took you to read this little explanation.

Knowledgebase Article 95,843 views bookmark tags: indexes mysql optimize optimizing queries query (updated 1010 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 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 1400 days ago)
Knowledgebase Article 84,565 views tags: indexes mysql optimize optimizing queries query

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 1400 days ago)
Knowledgebase Article 70,301 views tags: cpu exceeded index indexes mysql optimize optimizing queries query

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 o (updated 1401 days ago)
Knowledgebase Article 76,681 views tags: cpu exceeded index 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 63 days ago)
Knowledgebase Article 208,351 views tags: cpu database mysql optimize query script slow throttling

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

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

How can I optimize my images? (updated 2095 days ago)
Knowledgebase Article 92,679 views tags: images optimize

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

This article will explain how to connect your scripts to your MySQL databases. (updated 888 days ago)
Knowledgebase Article 28,162 views tags: database mysql scripts

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

This article will describe connections strings, what they are used for, and give examples. (updated 521 days ago)
Knowledgebase Article 69,179 views tags: connection databases mysql