Speed Up MySQL Using Indexes

What is an index?

Think of a big book. To find a topic, you can read every page, or you can look in the index at the back. The index tells you the page number at once.

A MySQL index works the same way. A database is a store for your site's information, kept in tables. A query is a question you ask the database. Without an index, MySQL reads every row to answer. With an index, it jumps straight to the right rows.

When indexes help

• Columns you search with WHERE.
• Columns you join tables on.
• Columns you sort by with ORDER BY.

Step 1: Find slow queries

Use EXPLAIN in front of a query. It shows how MySQL plans to run it.

EXPLAIN SELECT * FROM orders WHERE customer_id = 25;

Look at the type column. The value ALL means MySQL reads the whole table. That is slow on big tables. Also look at key. If it is empty, no index is used.

Step 2: Add an index

Back up your database first. You can run these in phpMyAdmin on the SQL tab.

CREATE INDEX idx_customer ON orders (customer_id);

This builds an index named idx_customer on the customer_id column.

To cover two columns used together, make a composite index:

CREATE INDEX idx_cust_date ON orders (customer_id, order_date);

Put the column you filter by most often first.

Step 3: Check again

1. Run EXPLAIN on the query again.
2. Check that key now shows your index name.
3. Check that the number in rows is much smaller.

See and remove indexes

SHOW INDEX FROM orders;
DROP INDEX idx_customer ON orders;

The first lists the indexes on a table. The second deletes one. Dropping an index never deletes your rows.

Do not add too many

Indexes make reading faster but writing slower. Each insert or update must also update the indexes. They also use disk space. Add only those you really need.

Quick recap

• An index lets MySQL find rows quickly, like a book index.
• Use EXPLAIN to find queries that scan the whole table.
• Add indexes on columns used in WHERE, joins and sorting.
• Do not over-index, because it slows writes.
• Back up before changing tables.