Using Indexes Effectively

Tables with too few indexes will return results very slowly. But adding too many indexes, although less common, is still problematic. They take up disk space. And, because an index is sorted, each time an insert or an update is performed, the index has to be re-sorted to include the changes, resulting in a significant extra load. The following sections explain when you should and shouldn't use indexes. Also note that index efficiency is dependent on the configuration; see Chapter 13, "Configuring and Optimizing MySQL," for more on this.

Where Are Indexes Used?

The most common use of an index is to retrieve rows that match a condition in a WHERE clause:

mysql> SELECT first_name FROM customer WHERE surname>'C'; +------------+ | first_name | +------------+ | Yvonne     | | Johnny     | | Winston    | | Patricia   | | Francois   | | Winnie     | | Breyton    | +------------+ 7 rows in set (0.00 sec)

An index on the surname field would be useful in this example. An index on the first_ name field would not be used in this query, as it is not part of the condition. Any fields appearing only in the field list (immediately after the SELECT) do not make use of an index.

When searching for the MAX() or MIN() values, MySQL only needs to look at the first or last value in the sorted index table, which is extremely quick. If there are frequent requests for MAX() or MIN() values, an index on the appropriate field would be extremely useful.

mysql> SELECT MAX(id) FROM customer; +---------+ | MAX(id) | +---------+ |      10 | +---------+ 

An index on the id field would assist this query tremendously.

There's another case where MySQL never needs to look at the full table and can just look at the index: when all the fields to be returned are part of an index. Take this example:

mysql> SELECT id FROM customer; +----+ | id | +----+ |  1 | |  2 | |  3 | |  4 | |  5 | |  6 | |  7 | | 10 | +----+ 8 rows in set (0.01 sec)

If the id field were indexed here, MySQL would never even need to look at the data file. This would not apply if the index consisted only of a portion of the full column data (for example, the field is a VARCHAR of 20 characters, but the index was created on the first 10 characters only).

Another case where an index is useful is where you ORDER BY a field, as with this example:

mysql> SELECT * FROM customer ORDER BY surname; +----+------------+-------------+ | id | first_name | surname     | +----+------------+-------------+ |  6 | Neil       | Beneke      | |  2 | Johnny     | Chaka-Chaka | |  1 | Yvonne     | Clegg       | |  7 | Winnie     | Dlamini     | |  4 | Patricia   | Mankunku    | |  5 | Francois   | Papo        | |  3 | Winston    | Powers      | | 10 | Breyton    | Tshbalala   | +----+------------+-------------+ 8 rows in set (0.01 sec)

Because the records are returned in sorted order, which is exactly what an index is, an index on the surname field would be useful for this query. If the ORDER BY is DESC, the index is simply read in reverse order.

Warning 

Currently, indexes cannot be used in ORDER BY clauses with HEAP tables.

Indexes are employed to speed up joins as well, as in this example:

mysql> SELECT first_name,surname,commission FROM sales,sales_rep  WHERE code=8 AND sales.sales_rep=sales_rep.employee_number; +------------+---------+------------+ | first_name | surname | commission | +------------+---------+------------+ | Mike       | Serote  |         10 | +------------+---------+------------+ 1 row in set (0.00 sec) 

An index would be used to perform the join condition—in other words, to look up both the sales.sales_rep and sales_rep.employee_number fields. This of course applies even if you use the alternative syntax:

mysql> SELECT first_name,surname,commission FROM sales INNER  JOIN sales_rep ON sales.sales_rep=sales_rep.employee_number  WHERE code=8;                                               +------------+---------+------------+ | first_name | surname | commission | +------------+---------+------------+ | Mike       | Serote  |         10 | +------------+---------+------------+ 1 row in set (0.00 sec)

An index can be used when your query condition contains a wildcard, such as:

mysql> SELECT * FROM sales_rep WHERE surname LIKE 'Ser%'; +------------+---------+------------+ | first_name | surname | commission | +------------+---------+------------+ | Mike       | Serote  |         10 | +------------+---------+------------+ 1 row in set (0.00 sec)

But an index cannot be used in the following case:

mysql> SELECT * FROM sales_rep WHERE surname LIKE '%Ser%'; +------------+---------+------------+ | first_name | surname | commission | +------------+---------+------------+ | Mike       | Serote  |         10 | +------------+---------+------------+ 1 row in set (0.00 sec)

The difference is that the latter has a wildcard as the first character. Because the index is sorted alphabetically from the first character, the presence of the wildcard renders the index useless.

Choosing Indexes

Now that you know where MySQL uses indexes, here are a few tips to help you with choosing indexes.

  • It should go without saying that you should only create indexes where you have queries that will make use of them (on fields in your WHERE condition, for example), and not on fields where they will not be used (such as where the first character of the condition is a wildcard).

  • Create indexes that return as few rows as possible. A primary key is the best here, as each primary key is uniquely associated with one record. Similarly, indexes on enumerated fields will not be particularly useful (for example, an index on a field containing the values yes or no would only serve to reduce the selection to half, with all the overhead of maintaining an index).

  • Use short indexes (index only the first 10 characters of a name, for example, rather than the entire field).

  • Don't create too many indexes. An index adds to the time to update or add a record, so if the index is for a rarely used query that can afford to run slightly more slowly, consider not creating the index.

  • Make use of leftmost prefixing (see the next section).

Using Leftmost Prefixes

You already know you can create an index on more than one field. Surnames and first names are good examples of this, so that, although there may be many duplicate surnames, the first name would make the index close to unique. In effect, there are two indexes available to MySQL here. The first is surname and first_name, and the second is just surname. Starting from the left of the list of fields in the index, MySQL can use each of these in turn, as long as they keep to the sequence starting from the left. Some examples will make this clearer. Let's add an initial field to the customer table and add some values, as well as an index:

mysql> ALTER TABLE customer ADD initial VARCHAR(5); Query OK, 8 rows affected (0.01 sec) Records: 8  Duplicates: 0  Warnings: 0 mysql> ALTER TABLE customer ADD INDEX (surname,initial, first_name); Query OK, 8 rows affected (0.01 sec) Records: 8  Duplicates: 0  Warnings: 0 mysql> UPDATE customer SET initial='X' WHERE id=1; Query OK, 1 row affected (0.00 sec) Rows matched: 1  Changed: 1  Warnings: 0 mysql> UPDATE customer SET initial='B' WHERE id=2; Query OK, 1 row affected (0.00 sec) Rows matched: 1  Changed: 1  Warnings: 0 mysql> UPDATE customer SET initial='M' WHERE id=3; Query OK, 1 row affected (0.00 sec) Rows matched: 1  Changed: 1  Warnings: 0 mysql> UPDATE customer SET initial='C' WHERE id=4; Query OK, 1 row affected (0.00 sec) Rows matched: 1  Changed: 1  Warnings: 0 mysql> UPDATE customer SET initial='P' WHERE id=5; Query OK, 1 row affected (0.00 sec) Rows matched: 1  Changed: 1  Warnings: 0 mysql> UPDATE customer SET initial='B' WHERE id=10; Query OK, 1 row affected (0.01 sec) Rows matched: 1  Changed: 1  Warnings: 0 

If you performed a query with all three of these fields in the condition, you'd be making the most use of this index:

mysql> SELECT * FROM customer WHERE surname='Clegg' AND  initial='X' AND first_name='Yvonne'; 

You would also make the most of the index if you searched for surname and initial:

mysql> SELECT * FROM customer WHERE surname='Clegg' AND initial='X'; 

or just surname:

mysql> SELECT * FROM customer WHERE surname='Clegg'; 

However, if you broke the leftmost sequence and searched for either first name or initial, or both first name and initial, MySQL would not use the index. For example, none of the following makes use of an index:

mysql> SELECT * FROM customer WHERE initial='X' AND first_name='Yvonne'; mysql> SELECT * FROM customer WHERE first_name='Yvonne'; mysql> SELECT * FROM customer WHERE initial='X'; 

If you searched on the first and third fields of the index (surname and first_name), you'd be breaking the sequence, so the index would not be fully used. However, because surname is the first part of the index, this portion of the index would still be used:

mysql> SELECT * FROM customer WHERE surname='Clegg' AND first_name='Yvonne'; +----+------------+---------+---------+ | id | first_name | surname | initial | +----+------------+---------+---------+ |  1 | Yvonne     | Clegg   | X       | +----+------------+---------+---------+ 1 row in set (0.00 sec) 

You can use leftmost prefixing whenever an index is used, such as with an ORDER BY clause.



Mastering MySQL 4
Mastering MySQL 4
ISBN: 0782141625
EAN: 2147483647
Year: 2003
Pages: 230
Authors: Ian Gilfillan

flylib.com © 2008-2017.
If you may any questions please contact us: flylib@qtcs.net