An index is a sorted list of one column (or several) that the database consults instead of reading the whole table. To find a customer by e-mail, with no index it walks row by row; with an index it goes straight to the spot. The trade is this: reads get faster, writes get a little slower and the index takes space. That is why you add few, and chosen ones.
This article shows how to pick the type and add the index. To find out which query needs one, start with slow queries: how to find them.
The types you will meet
| Type |
What it is for |
| PRIMARY |
Identifies each row. Only one per table, with no repeats and no empties. Almost always the id column. |
| UNIQUE |
Stops two equal values, for example a user’s e-mail. Besides being fast, it works as a rule. |
| INDEX (normal) |
Speeds up searches and sorting on a column that repeats: an order’s status, a date, a category. |
| FULLTEXT |
Searches by words inside long text. A different tool, with a different query syntax; it does not replace the normal index. |
A multi-column index: the order matters
An index on (status, date) helps queries that filter by status, or by status and date. It does not help a query that filters by date alone. It works like a phone book sorted by surname and then by first name: it serves a search by surname, not by first name on its own. Rule of thumb: put first the column the query compares with equals, then the one it uses a range on.
Adding an index, step by step
| 2 |
In cPanel open phpMyAdmin, pick the database in the left column and then the table.
|
|
| 3 |
Open the Structure tab. Lower down you see the list of indexes that already exist: check that the column does not have one yet, or that the first does not already cover it.
|
|
| 4 |
For an index on a single column, use the Index option on that column’s row. For several, use the “create an index” field below the list, enter the number of columns, pick them in the right order and give it a name.
|
|
| 5 |
From the SQL tab it is the same, in one line: ALTER TABLE orders ADD INDEX idx_status (status);. To remove it: ALTER TABLE orders DROP INDEX idx_status;.
|
|
| 6 |
Measure again. Repeat the query’s EXPLAIN and see whether it now uses the index. Without measuring you cannot know it helped.
|
|
|
More indexes is not better. Each one takes space and slows every insert and update. Do not index every column, nor long text columns without need (on text columns you index only the start, for example description(50)). On a huge table, building the index takes time: do it outside the busiest hours.
|
|
WordPress and shop tables: do not add indexes to core tables on a hunch. If a slow plugin’s documentation gives an index, follow it. If not, the problem is usually the plugin’s query, and the cure is another one: see the database has grown: what can go.
|
|
Have a slow query and already measured it with EXPLAIN? Send us the result and the table, and we will look at it with you.
Open a support ticket
|
RECOMMENDED PRODUCT Web hosting with cPanel Domain and SSL included, daily backups and the panel you already know. from $6.59/mo (3-year plan, with coupon) See plans |
Português
English
