As things stand, the table classics works and can be searched without problem by MySQL—until it grows to more than a couple hundred rows, that is. At that point, database accesses will get slower and slower with every new row added, because MySQL has to search through every row whenever a query is issued. This is like searching through every book in a library whenever you need to look something up.
Of course, you don’t have to search libraries that way, because they have either a card index system or, most likely, a database of their own. And the same goes for MySQL,because at the expense of a slight overhead in memory and disk space, you can create a “card index” for a table that MySQL will use to conduct lightning-fast searches.
Creating an Index
The way to achieve fast searches is to add an index, either when creating a table or at any time afterward. But the decision is not so simple. For example, there are different index types, such as INDEX, PRIMARY KEY, and FULLTEXT. Also you must decide which columns require an index, a judgment that requires you to predict whether you will be searching any of the data in that column. Indexes can also get complicated, because you can combine multiple columns in one index. And even when you’ve decided that, you still have the option of reducing index size by limiting the amount of each column to be indexed.
Let’s and add an index to each of the columns, using the commands in following example.
ALTER TABLE classics ADD INDEX(author(20)); ALTER TABLE classics ADD INDEX(title(20)); ALTER TABLE classics ADD INDEX(category(4)); ALTER TABLE classics ADD INDEX(year); DESCRIBE classics;
Using CREATE INDEX
An alternative to using ALTER TABLE to add an index is to use the CREATE INDEX command.They are equivalent, except that CREATE INDEX cannot be used to create a PRIMARY KEY (see the section “Primary keys” on page 180). The format of this command can be seen in the second line of following Example.These two commands are equivalent.
ALTER TABLE classics ADD INDEX(author(20)); CREATE INDEX author ON classics (author(20));
Adding indexes when creating tables
You don’t have to wait until after creating a table to add indexes. In fact, doing so can be time-consuming, as adding an index to a large table can take a very long time. Therefore, let’s look at a command that creates the table classics with indexes already in place.
Consider the following example :
CREATE TABLE classics ( author VARCHAR(128), title VARCHAR(128), category VARCHAR(16), year SMALLINT, INDEX(author(20)), INDEX(title(20)), INDEX(category(4)), INDEX(year)) ENGINE MyISAM;