Publishers of technology books, eBooks, and videos for creative people

Home > Articles > Web Design & Development

  • Print
  • + Share This
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.

  • + Share This
  • 🔖 Save To Your Account