About two years into building PHP applications seriously, I had a client project — a small inventory management system — that started performing perfectly fine in development and then basically crawled to a halt about three months after launch. Page loads that were under 200ms locally were hitting 4–6 seconds in production. Users were complaining. The client was frustrated. And I had no idea why, because the code was exactly the same.
The culprit, eventually, was MySQL indexing. Or rather, the total absence of it in places that mattered. I had a products table with around 18,000 rows, a transactions table with over 60,000 rows, and I was joining them on unindexed columns in a report query that ran every time someone opened the dashboard. MySQL was scanning every single row, every single time. I was effectively asking it to read an entire filing cabinet to find one document, hundreds of times per day.
What I learned from fixing that, and from the five or six similar situations I've encountered since, is that MySQL indexing is one of the most impactful performance tools available to PHP developers — and also one of the most consistently misunderstood. Not because it's technically complex, but because the mental model most people carry around is wrong from the start.
The Mental Model Problem
Here's the faulty assumption I held for years: indexes are an optimization you reach for when something is already slow. You notice a slow page, you add an index, it gets faster. Done.
That's not wrong exactly, but it's dangerously incomplete. Because by the time you're noticing slowness, you've often already compounded the problem. Tables have grown. Bad query patterns have calcified into your codebase. And worse — you start reaching for indexes indiscriminately, adding them to every column you query, thinking more is better.
More is not better. I've seen tables where well-meaning developers added indexes on every column "just in case," and then wondered why bulk inserts were timing out. Every index you add is a data structure MySQL has to maintain on every write. On a table that receives thousands of inserts per day, gratuitous indexing will noticeably degrade write performance. The goal is surgical precision, not carpet bombing.
What EXPLAIN Actually Tells You (And Why Most People Ignore It)
The single most useful tool for understanding MySQL query performance is EXPLAIN, and in five years of talking to other PHP developers, I'd estimate maybe 20% of them use it regularly. That's a shame, because it's right there.
The basic usage is simple: prepend EXPLAIN to any SELECT statement and MySQL returns a row of metadata about how it plans to execute the query. The columns I look at first are type, key, rows, and Extra.
- type: ALL — This is the red flag. Full table scan. MySQL is reading every row.
- type: index — Full index scan. Still potentially slow on large tables, but better than ALL.
- type: range — Index range scan. Solid. MySQL is using an index and narrowing results.
- type: ref or eq_ref — MySQL is using an index to look up specific rows. This is what you want.
- key: NULL — No index is being used for this part of the query.
- Extra: Using filesort — MySQL can't use an index for sorting, so it's doing it in memory or on disk. Expensive.
- Extra: Using temporary — MySQL is creating a temp table. Often appears with GROUP BY or ORDER BY on unindexed columns.
In my inventory project, running EXPLAIN on the dashboard report query showed type: ALL on both joined tables and rows: 60421 on the transactions side. MySQL was examining over 60,000 rows for every dashboard load. The fix — adding a composite index on the join columns — dropped that to rows: 12 and query time from 3.8 seconds to under 80ms. Same query, same data, completely different execution plan.
The Indexes That Actually Matter in Real PHP Projects
Foreign Key Columns That You're Joining On
This is the most common missed index I see. You define a foreign key relationship, MySQL enforces the constraint, but the constraint itself does not create an index. You have to do that separately. Every column used in a JOIN that isn't the primary key should have an index unless the table is tiny.
-- You have this:
SELECT p.name, c.name as category
FROM products p
JOIN categories c ON c.id = p.category_id
WHERE p.status = 'active';
-- Check whether category_id and status are indexed:
SHOW INDEX FROM products;If category_id and status don't appear in that output, you're scanning. Add them.
Composite Indexes for Compound WHERE Clauses
Here's something that took me a while to internalize: two separate single-column indexes are not the same as one composite index. MySQL can generally only use one index per table per query (with some exceptions using index merge). If you frequently filter by both status and created_at, a composite index (status, created_at) will outperform two separate indexes.
Column order in a composite index matters. Put the highest-cardinality, most-selective column first — typically the one with the most distinct values. A column like status with only three possible values is low cardinality. A column like user_id with thousands of distinct values is high cardinality. In a composite, high-cardinality columns generally go first unless your query pattern forces otherwise.
Columns in ORDER BY and GROUP BY
This one gets overlooked constantly. If you're ordering results by created_at DESC on every listing page, and created_at has no index, MySQL is sorting the full result set in memory or on disk — that "Using filesort" flag in EXPLAIN. Indexing the sort column lets MySQL return rows in index order without the sort step.
Partial Indexes for TEXT and VARCHAR Columns
MySQL can't index a full TEXT column directly. For long VARCHAR columns, indexing the full length is wasteful. Use a prefix index instead:
ALTER TABLE users ADD INDEX idx_email_prefix (email(20));For search use-cases, consider FULLTEXT indexes rather than LIKE queries with leading wildcards. LIKE '%keyword%' cannot use a standard index. It scans everything. I've replaced several of these with MATCH() AGAINST() on FULLTEXT-indexed columns and seen dramatic improvements.
The Write-Performance Tradeoff You Can't Ignore
I mentioned this earlier but it deserves its own section because it's where the real judgment calls happen. Every index is a cost paid on writes to buy performance on reads. In an application that's read-heavy — a public-facing product catalog, a dashboard, a reporting tool — that's a good trade. In an application that's write-heavy — a logging system, a queue processor, a real-time event tracker — it might not be.
I once had a job queue table that was getting hammered with inserts. Someone had added four indexes to it for various filtering queries in an admin panel that ran twice a day. The inserts were backing up because MySQL was maintaining four index structures on every row write. Dropping three of those indexes and rewriting the admin query to use a different approach cut insert latency by 65%. The admin report was now slightly slower — maybe 400ms instead of 90ms — but that was an acceptable tradeoff for a query that ran twice a day versus inserts happening thousands of times per hour.
This is the analysis you have to do. Ask: how frequently does this table get written to? How frequently is this column queried? What's the cost of a slow read versus a slow write in this specific context?
Finding Slow Queries on Shared cPanel Hosting
On shared hosting, you typically can't enable the MySQL slow query log yourself — that's a server-level setting. But you're not helpless. The two approaches that work reliably for me:
Manual timing with PDO. I wrap query execution in microtime(true) calls during development and log anything over a threshold. It's crude but it catches the obvious offenders fast.
$start = microtime(true);
$stmt = $pdo->prepare("SELECT ...");
$stmt->execute($params);
$elapsed = microtime(true) - $start;
if ($elapsed > 0.5) {
error_log("Slow query ({$elapsed}s): " . $stmt->queryString);
}EXPLAIN during development, always. Before any feature goes to production, I run EXPLAIN on every new query and check for full table scans. This catches problems before the table has 60,000 rows, when they're much cheaper to fix.
If you're working through PHP debugging more broadly, I wrote about production debugging approaches in How I Debug PHP Errors on Production Without Losing My Mind (or My Users) — the same principle applies here: instrument before you need it, don't wait for users to report pain.
One More Thing: Index Maintenance
Indexes degrade over time as data is inserted, updated, and deleted. MySQL calls this index fragmentation. On busy tables, running ANALYZE TABLE tablename; periodically updates the index statistics MySQL uses for query planning. Running OPTIMIZE TABLE tablename; rebuilds the table and indexes, reclaiming fragmented space — though be warned, it locks the table during the operation, so schedule it during low-traffic periods.
I've seen cases where the query plan MySQL chose was genuinely suboptimal because its statistics were stale. Running ANALYZE fixed it without any schema changes. It's worth adding to your periodic maintenance checklist, especially on tables that churn a lot of data.
The Takeaway
MySQL indexing isn't magic and it isn't free. It's a deliberate set of tradeoffs: read speed versus write speed, query simplicity versus schema complexity, storage cost versus response time. The developers who get this right aren't the ones who add indexes everywhere — they're the ones who read EXPLAIN output, think about their access patterns, and make conscious decisions.
Start with EXPLAIN on every significant query before it ships. Index foreign key join columns. Build composite indexes that match your actual WHERE clause patterns. Watch for "Using filesort" and "Using temporary" — those are your most actionable signals. And remember that three well-chosen indexes will outperform twelve reflexively added ones every time.
If your PHP app is using PDO and prepared statements (which it should be — and if you're thinking about query security alongside performance, those two concerns overlap more than people realize), the same mindset applies: understand what the database is actually doing, not just what you asked it to do. The gap between those two things is where most performance problems live.
Frequently Asked Questions
When should I add an index to a MySQL table in PHP projects?
Add an index when a column appears frequently in WHERE, JOIN, or ORDER BY clauses and the table has more than a few hundred rows. The clearest signal is a slow query log entry or an EXPLAIN output showing a full table scan (type: ALL). Don't index everything preemptively — unused indexes cost write performance and storage.
Does adding more indexes always speed up MySQL queries?
No, and this is the most common misconception. Every index you add slows down INSERT, UPDATE, and DELETE operations because MySQL must maintain the index structure on every write. The goal is the minimum set of indexes that eliminate your most expensive reads.
How do I find slow queries in a PHP app on shared cPanel hosting?
On cPanel hosting you usually can't enable the slow query log directly, but you can use EXPLAIN in your PHP code during development to inspect query plans, or log query execution time manually with microtime() around your PDO calls. Some hosts expose MySQL slow query logs in the Metrics section of WHM if you have reseller access.
