Speed up slow database queries by adding an index

If a specific page or operation on your site is slow and you have traced it to a slow database query, adding the right index is often the single biggest improvement you can make - frequently turning a multi-second query into a millisecond one. This guide shows you how to confirm an index will help, how to add one in phpMyAdmin, and how to verify it worked.

What an index is

An index is a separate data structure that MySQL maintains alongside a table. Think of it like the index at the back of a book: instead of reading every page to find a topic, you look it up and jump straight to the right page. Without an index on a column you are searching, MySQL reads every row in the table, every time - fine at a thousand rows, painful at a million.

Your application's core tables ship with sensible indexes. The gaps almost always appear in tables created by plugins, extensions, or custom code, where the developer never anticipated how large the table would get or how it would be queried.

Start with the slow query, not the index

Indexes fix specific queries, so identify the query first. Where to look depends on your application: for WordPress, the Query Monitor plugin lists every query on a page with timings and which plugin ran it; most frameworks have an equivalent debug mode. If you cannot identify the query yourself, we can enable the slow query log for your account, which records every query over a time threshold - just open a support ticket and ask.

Ask MySQL whether an index would help

You do not need to guess. MySQL will tell you exactly how it plans to execute a query. Open phpMyAdmin from the Databases section of cPanel, select your database, open the SQL tab, and prefix the slow query with EXPLAIN:

EXPLAIN SELECT * FROM orders WHERE customer_email = '[email protected]';

Two columns in the result matter:

• rows - MySQL's estimate of how many rows it must examine.
• key - the index it plans to use.

A large rows estimate with an empty key column means MySQL is scanning the whole table. That is the clearest possible signal that an index on the column in your WHERE clause will help.

Add the index in phpMyAdmin

1. In phpMyAdmin, select your database and click the table you want to work with.
2. Click the Structure tab to see the list of columns.
3. Find the column your query filters on and choose the Index action for it (in some phpMyAdmin versions this is under the More dropdown on the column's row).
4. Review the index name phpMyAdmin suggests and click Go.

Indexes on more than one column

If your query filters on two or more columns together - for example WHERE customer_id = 42 AND status = 'pending' - a single index covering both columns beats two separate ones. Use Add index at the bottom of the Structure tab, select the columns, and pay attention to the order: MySQL can use a multi-column index for queries filtering on the first column alone, or the first and second together, but not the second alone. Put the column you always filter on first.

Verify it worked

Run the same EXPLAIN again. The key column should now show your new index, and the rows estimate should have dropped dramatically - often from hundreds of thousands to single digits. Then load the slow page and feel the difference.

When an index will not help

A few query patterns cannot use an ordinary index, and recognising them saves you from adding indexes that do nothing:

• Leading-wildcard searches such as LIKE '%searchterm%'. An index works like alphabetical order - it can find everything starting with a value, but not everything containing one. Queries like this on large tables need a different approach, such as full-text search.
• Functions wrapped around the column, such as WHERE YEAR(created_at) = 2026. MySQL cannot use an index on created_at here because every row must be run through the function first. Rewriting the query as a date range fixes it.
• Queries that genuinely need most of the table, such as unfiltered reports. No index helps a query that has to read everything anyway.

If your slow query falls into one of these patterns, the fix is changing the query rather than the table - that is one for the plugin or extension developer, though we are happy to confirm the diagnosis for you.

A few words of restraint

Indexes speed up reads but add a small cost to every insert, update, and delete, and they consume disk space. Do not index every column just in case - add indexes for the queries you have actually identified as slow, and let EXPLAIN be the judge.

When to contact us

If you have found a slow query but are unsure how to interpret the EXPLAIN output, or the query is slow even though it is using an index, open a support ticket with the query and the EXPLAIN result included. We can also enable and analyse the slow query log for your account, and advise whether the problem is the query, the table, or something at the server level.