This chapter is from the book
This chapter is from the book
This chapter is from the book
Indexes
Indexes are a special system that databases use to improve the overall performance. By setting indexes on your tables, you are telling MySQL to pay particular attention to that column (in layman's terms). In fact, MySQL creates extra files to store and track indexes efficiently.
MySQL allows for up to 32 indexes for each table, and each index can incorporate up to 16 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). 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)
Note that, in MySQL, a primary key column is automatically indexed for efficiency.
MySQL has three types of indexes: INDEX, UNIQUE (which requires each row to have a unique value), and PRIMARY KEY (which is just a particular UNIQUE index). Table 3.5 lists the indexes I propose for the accounting database.
Table 3.5 To improve the performance of my database, I add a few (but not too many) indexes to help MySQL access the stored information.
Accounting Indexes |
|
|
Column |
Index Type |
|
Invoice Number |
PRIMARY KEY |
|
Client ID |
PRIMARY KEY |
|
Expense ID |
PRIMARY KEY |
|
Expense Category ID |
PRIMARY KEY |
|
Invoice Date |
INDEX |
|
Client Name |
INDEX (or UNIQUE) |
One final attribute a column can have that frequently works in conjunction with an index is AUTO_INCREMENT. When you define a field with this property using client_id SMALLINT(3) UNSIGNED NOT NULL AUTO_INCREMENT you are effectively telling MySQL to set the value of this column to the next logical value in the series. If the column is an integer, the next highest integer will be used when no value is set when a new record is inserted.