Introduction to Indexes

Indexes are a special system that databases use to improve overall performance. By setting indexes on your tables, you are telling MySQL to pay particular attention to that column or those columns (in layman's terms).

MySQL allows for between 16 and 64 indexes for each table, depending upon the storage engine being used. Each index can be on anywhere from 1 to 15 columns. While a multicolumn index may not seem obvious, it will come in handy for searches frequently performed on the same set of multiple columns (e.g., first and last name, city and state, etc.).

On the other hand, one should not go overboard with indexing. While it does improve the speed of reading from databases, it slows down the process of altering data in a database (because the changes need to be recorded in the index). On the other hand, you'll normally retrieve data from a database much more often than you'll insert new or update existing data.

Indexes are best used on columns

• That are frequently used in the WHERE part of a query

• That are frequently used in an ORDER BY part of a query

• That have many different values (columns with numerous repeating values ought not to be indexed)

• That are frequently used in a JOIN.

MySQL has a few different types of indexes: INDEX, UNIQUE (which requires each row to have a unique value), and PRIMARY KEY (which is just a particular UNIQUE index). There is also a FULLTEXT index, which is discussed in Chapter 10, "Advanced SQL and MySQL." Table 4.6 lists the indexes I propose for the accounting database after these steps.

Table 4.6. To improve the performance of my database, I add a several indexes to help MySQL access the stored information. (This may be an excessive number of indexes, depending upon how the application will be used.) Accounting Database Indexes

Accounting Database Indexes
Column	Table	Index Type
Invoice Number	Invoices	PRIMARY KEY
Client ID	Invoices	INDEX
Invoice Date	Invoices	INDEX
Invoice Amount	Invoices	INDEX
Date Invoice Paid	Invoices	INDEX
Client ID	Clients	PRIMARY KEY
Client Name	Clients	INDEX (or UNIQUE)
Expense ID	Expenses	PRIMARY KEY
Expense Category ID	Expenses	INDEX
Expense Amount	Expenses	INDEX
Expense Date	Expenses	INDEX
Expense Category ID	Expense Categories	PRIMARY KEY
Expense Category	Expense Categories	UNIQUE

To add indexes:

1.	1. Identify any columns that should be marked as the PRIMARY KEY. This should be obvious, particularly if you followed the normalization steps taught in the previous chapter. Remember that there will only ever be one primary key per table (although it is possible that the primary key is a composite of multiple columns).
2.	2. Identify any remaining columns whose values must always be UNIQUE. The UNIQUE index type is also not used excessively. Most valuesdates, numbers, names, cities, zip codesmay repeat, particularly in tables with thousands upon thousands of rows. But depending upon your application, you will find the occasional column that must be UNIQUE, like an email address, a user name (for a registration/login system), or the Expense Category field in the Expense Categories table. You would not define a PRIMARY KEY column as also being UNIQUE, because the PRIMARY KEY designation implies uniqueness.
3.	3. Identify any remaining columns that could benefit from an index. Use the preceding recommendations for when an index would help and think about what information will be retrieved. If you'll want to see a list of invoices in date or total amount order, those make for logical indexes. If a registration/login table will use the combination of the username and password to verify a login, that should be indexed. You'll also normally want to index your foreign key columns.

Tip

• Indexes are less efficient on variable-length columns, just as MySQL is generally slower dealing with fields that are not of a fixed length. You can compensate for this by indexing only a part of a variable-length column, like the first five or ten characters.