Managing & Using MySQL, 2nd Edition

SQL Query Tuning

The data in your database is stored on your disk. Retrieving and updating the data is ultimately a series of disk input/output operations (I/Os). The goal of SQL query tuning is to minimize the number of I/Os. Your main weapon for tuning your queries is the index.

In the absence of indexes on your database tables, each retrieval would require that all the data in all of the involved tables be scanned. To illustrate this problem, consider the following example:

SELECT name FROM Employee WHERE ssnum = 999999999

In this example, we select the name of an employee from the Employee table for the employee with 999-99-9999 as a social security number (ssnum). We know the social security number should be unique. In other words, for each record in the table, ssnum will have a unique value. We thus expect a single row from the above query since only one row can have the social security number of 999-99-9999.

Because the Employee table in our example has no indexes, MySQL does not know that the query will return a single record. When it executes the query, it has to scan the entire table to find all the records that match the WHERE clause—a scan of the entire table for the one record with a social security number of 999-99-9999. If the Employee table has a thousand rows, MySQL will read each and every one of those rows to compare the ssnum value to the constant 999999999. This operation is linear with the number of rows in the table.

An index is a tool for telling MySQL critical ...