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!
IE 7
Firefox 2.0