Sams Teach Yourself MySQL in 21 Days, Second Edition

Indexes

An index, also known as a key, provides a means of rapidly accessing specific rows of data within a table.

Indexes Explained

Suppose that you have a users table that holds details about many people. When you ask MySQL to retrieve all rows matching a given criterion, it reads through the entire table, row by row, retrieving data from all rows that match the criterion. Even if you want to retrieve only a single specific row, it must read every row of the table.

Reading through an entire table like this—an operation known as a table scan—does the job but is highly inefficient. That's where an index can help.

Say that we have a query in which we want to retrieve the row referenced by a given user_id in table users. The table might look like ...