Indexing for Optimization

If you have been following along in this book, you should have a database that contains primary keys. This will ensure that you have at least one index per table because indexes are automatically created by MySQL for columns that are declared as PRIMARY , KEY , or UNIQUE .

If you are trying to optimize an existing database, it is worth checking whether the database has any indexes. It is a common design flaw to leave them out. The SQL command DESCRIBE will tell you what indexes a table already has.

So, what's an index, and what is it used for?

An index is like a lookup table that allows us to find specific rows in a table quickly. If an index is created on column X, we can search for particular values of column X in the fast-to-search index. The index will tell us where in the table the row containing that value can be found, so we can go directly to it.

If you do not have an index on a table, the entire table will be scanned to find rows you are looking for. Imagine trying to find a topic in this book by starting at the beginning and reading every word on every page. It is much faster to look up a topic in the index of this book and turn directly to the page you need.

Indexes in MySQL are stored as b-trees (binary trees), a data structure that is very fast for searching.

Indexes can be on a single column or can span multiple columns (just like keys). An index will be used when running a query, if the search is being performed on the following:

• A single column that has a single-column index ”for example, if we index departments on departmentID and perform a query like SELECT...WHERE departmentID= n .

• A set of columns that forms a multicolumn index ”for example, if we have created an index on the employee.assignment table on (clientID, employeeID, workdate) and we perform a query like SELECT...WHERE clientID= x AND employeeID= y AND workdate= z .

• A column or set of columns that forms a subset of a multicolumn index, as long as there is a leftmost prefix of the index columns ”for example, with the assignment table as before, with an index on (clientID, employeeID, workdate) , indexes would be used for these types of queries:

   SELECT...WHERE clientID=  x  SELECT...WHERE clientID=  x  AND employeeID=  y  

  But, they would not be used for this type:

   SELECT...WHERE employeeID=  y  AND workdate=  z  

The moral of the story is that is if you will be making frequent queries based on a column or set of columns that does not fit the preceding criteria, you should consider running a CREATE INDEX statement to create an appropriate index. Note that MySQL can use only one index per table in a single query. It cannot combine existing indexes automatically.

We will look further at this issue in Chapter 19, "Optimizing Your Queries," when we look at the EXPLAIN statement.