This site utilizes JavaScript to function correctly. Looks like it's disabled on your browser. Please enable it for your best experience.

For instructions on enabling JavaScript, click here

Skip to main content

Just Host Web Hosting Help

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 with an index, you have to update the index as well, so there is a performance price to pay. But unless your system runs many more inserts than selects and the inserts need to be quick, and not the selects, this is a price worth paying.

What about if you want to select on more than one criteria? (As you can see, it only makes sense to index those fields you use in the WHERE clause.) The query:

SELECT firstname FROM employee;

makes no use of an index at all. An index on firstname is useless. But,

SELECT firstname FROM employee WHERE surname="Madida";

would benefit from an index on surname.

Let's look at some more complex examples where EXPLAIN can help us improve the query. We want to find all the employees where half their overtime rate is less than $20. Knowing what you do, you correctly decide to add an index on overtime_rate, seeing as that's the column in the where clause.

ALTER TABLE employee ADD INDEX(overtime_rate);

Now let's run the query.

EXPLAIN SELECT firstname FROM employee WHERE overtime_rate/2<20;

+----------+------+---------------+------+---------+------+------+------------+
            | table    | type | possible_keys | key  | key_len | ref  | rows | Extra      |
            +----------+------+---------------+------+---------+------+------+------------+
            | employee | ALL  | NULL          | NULL |    NULL | NULL |    2 | where used |
            +----------+------+---------------+------+---------+------+------+------------+
            

Not good at all! Every single employee record is being read. Why is this? The answer lies in the "overtime_rate/2" part of the query. Every overtime_rate (and hence every record) has to be read in order to divide it by 2. So we should try and leave the indexed field alone, and not perform any calculations on it. How is this possible? This is where your school algebra comes to the rescue! You know that 'x/2 = y' is the same as 'x = y*2'.We can rewrite this query, by seeing if the overtime_rate is less than 20*2. Let's see what happens.

EXPLAIN SELECT firstname FROM employee WHERE overtime_rate<20*2;

+--------+-------+---------------+---------------+---------+------+------+----------+
            |table   | type  | possible_keys | key           | key_len | ref  | rows |Extra     |
            +--------+-------+---------------+---------------+---------+------+------+----------+
            |employee| range | overtime_rate | overtime_rate |       4 | NULL |    1 |where used|
            +--------+-------+---------------+---------------+---------+------+------+----------+
            

Much better! MySQL can perform the 20*2 calculation once, and then search the index for this constant. The principle here is to keep your indexed field standing alone in the comparison, so that MySQL can use it to search, and not have to perform calculations on it.

You may say that I was being unfair, and should have phrased the request as "where the overtime rate is less than 40", but users seem to have a knack of making a request in the worst way possible!

Knowledgebase Article 187,089 views bookmark tags: 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 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
Knowledgebase Article 220,105 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
Knowledgebase Article 214,173 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
Knowledgebase Article 220,317 views tags: cpu exceeded index indexes mysql optimize optimizing queries query

Related Help Content

How to repair and optimize your database using phpMyAdmin:
Knowledgebase Article 441,528 views tags: database mysql optimizing repairing slow speed

A guide on performing a standard SQL query using the phpMyAdmin interface
Knowledgebase Article 191,095 views tags: databases mysql phpmyadmin

I would like to dump the Table Structure for my MySQL Database, but none of the data.
Knowledgebase Article 274,058 views tags: dump mysql ssh structure table

What is CPU Protection
Knowledgebase Article 385,635 views tags: cpu database mysql optimize peak performance query script slow

A tutorial on how to perminately delete columns from your mySQL database using phpMyAdmin
Knowledgebase Article 188,087 views tags: Plesk cpanel databases mysql phpmyadmin

A step by step guide on how to manipulate data in your mySQL tables using phpMyAdmin
Knowledgebase Article 198,668 views tags: databases mysql phpmyadmin

How can I optimize my images?
Knowledgebase Article 236,104 views tags: images optimize

This article will show you how to rename a database table in phpMyAdmin.
Knowledgebase Article 321,997 views tags: databases mysql phpmyadmin

** Google ad credits are only available to customers in the United States, Canada, and the United Kingdom at this time.

¹ VAT (Value Added Tax) is not included in our advertised price and will be charged separately and itemized on invoices and billing information. Standard VAT rates based on EU Member State regulations may apply. Learn more.