There is a way to speed up queries using “indexes”. Indexes are like a library card catalog. The index only contains very specific information that then points to the rest of the information. In a library, the card catalog would point to the book. You search quickly through the card catalog to get to a piece of information that you need, and then you go grab the book off the shelf.

Database indexing employs a similar system.

The downside to indexing is it takes up lots of memory. So you need to be specific with what you index. You can't index everything! Well, you can, but it wouldn't work well unless you had a boat load of memory in your system. A strategy I employ frequently is to create my tables and queries, build my software, and then go back and see what is the most frequently searched data. I then put an index on that data.

One quick note: Primary Keys are always indexed.

Let’s add an index to the names table on the lastName:

alter table names add index lastName (lastName);

If we describe the table,

mysql> describe names;
+-----------+------------------+------+-----+---------+----------------+
| Field     | Type             | Null | Key | Default | Extra          |
+-----------+------------------+------+-----+---------+----------------+
| id        | int(10) unsigned | NO   | PRI | NULL    | auto_increment |
| lastName  | varchar(252)     | NO   | MUL | NULL    |                |
| firstName | varchar(252)     | NO   |     | NULL    |                |
+-----------+------------------+------+-----+---------+----------------+
3 rows in set (0.00 sec)

We’ll see that lastName has MUL in the Key column. MUL basically indicates you have an index on that field.

We don’t need to do anything else. Any time we search on a last name, MySQL will automatically use the index to search. It’s all automatic in the background.