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.
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
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 ...