Faster searching with indexes

Databases usually start out small, allowing the database system to search through the records very quickly. As a database grows in size, so to does the time it takes the database to search through the information. We can create an index to help the database system find data more quickly and efficiently.

You will want to create indexes only on fields that are searched frequently. When you create an index for a field, updating of that table will take a slight bit longer because the index is updated as well.

The basic syntax for creating an index is:

CREATE INDEX index_name
ON table_name(field_name);

For example to create an index on the last_name field of the student table we could use the following code (I just named the index s_last_name):

CREATE INDEX s_last_name
ON student(last_name);

Lesson Activities

  1. Create an index on the last_name field of the student table.
    Click to see code
    CREATE INDEX s_last_name
    ON student(last_name);
  2. To view the index use the following command:
    Click to see code
    SHOW INDEX FROM student;
  3. Create an index on the title field of the book table.
    Click to see code
    CREATE INDEX b_title
    ON books(title);

Questions