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