Most slow MySQL queries are slow for the same reason: MySQL has to read far more rows than it returns. You fix that by writing queries that can use an index, and by giving them the right index. This page gives you the rules for writing faster queries; our full guide covers finding slow queries, reading EXPLAIN in depth and tuning a MySQL server of your own.
Put EXPLAIN in front of a slow SELECT. If it shows type: ALL or a large rows number, add an index that matches the WHERE and ORDER BY columns. Select only the columns you need, don't wrap indexed columns in functions, and page results with LIMIT. The old query cache no longer exists in MySQL 8, and on shared hosting you tune queries and indexes, not the server.
Finding slow queries, the EXPLAIN columns in detail, composite indexes and server settings for your own VPS are all in MySQL performance optimization: diagnosing and fixing slow queries. This page is the short version for people writing queries.
1. Check the plan with EXPLAIN
Run the query with EXPLAIN in front of it, in phpMyAdmin's SQL tab or any MySQL client:
EXPLAIN SELECT id, name, email
FROM users
WHERE email = '[email protected]';| Column | Good sign | Bad sign |
|---|---|---|
| type | const, eq_ref, ref, range | ALL (a full table scan) |
| key | The index you expected | NULL (no index used) |
| rows | Close to the rows you get back | Thousands for a handful of results |
| Extra | Using index, Using where | Using filesort, Using temporary |
On MySQL 8.0.18 and later, EXPLAIN ANALYZE runs the query and shows the real time spent in each step. It really executes the statement, so use it on SELECT only.
2. Give the query an index it can use
Index the columns your slowest, most frequent queries filter, join and sort on:
CREATE INDEX idx_users_email ON users (email);For a query that filters on one column and sorts on another, one composite index covers both. Put the = columns first and the sort or range column last:
-- serves: WHERE customer_id = ? ORDER BY created_at DESC
CREATE INDEX idx_orders_customer_created ON orders (customer_id, created_at);Every index slows INSERT, UPDATE and DELETE a little and uses disk space, so drop indexes that EXPLAIN never picks.
3. Write queries the index can serve
- Select only the columns you use.
SELECT name, emailinstead ofSELECT *, especially on tables with long text columns. - Keep indexed columns bare.
WHERE YEAR(created_at) = 2026can't use an index oncreated_at. Write a range instead:
SELECT id, total
FROM orders
WHERE created_at >= '2026-01-01' AND created_at < '2027-01-01';LIKE 'abc%'can use an index;LIKE '%abc%'can't. For word search, use aFULLTEXTindex withMATCH ... AGAINST.- Page with
LIMIT, and for deep pages use keyset paging (WHERE id < ? ORDER BY id DESC LIMIT 20) instead of a largeOFFSET, which still reads every skipped row. - Replace N+1 loops. One query per row inside a loop is a common hidden cost in plugins and ORMs. Fetch the rows in one query with a
JOINorWHERE id IN (...). - Use prepared statements (PDO or mysqli). They stop SQL injection and keep plans predictable.
4. Joins, subqueries and data types
Index the columns on both sides of a JOIN, usually the foreign key. Older guides say "always replace subqueries with joins". Current MySQL and MariaDB rewrite most IN (SELECT ...) subqueries into joins by themselves, so rewrite one only when EXPLAIN shows DEPENDENT SUBQUERY running once per row.
Pick the smallest data type that fits: integers for numeric IDs, CHAR for fixed-length codes, DATETIME or DATE for dates rather than strings. Use InnoDB for every table; MyISAM has no transactions and locks the whole table on each write.
5. What no longer applies
query_cache_* lines stop MySQL 8 from starting. Cache in your application instead.SHOW PROFILE is deprecated. Use EXPLAIN ANALYZE or the Performance Schema.SET GLOBAL slow_query_log and my.cnf changes need a server you manage yourself.6. Running this on Domain India
Shared hosting (cPanel, DirectAdmin, Webuzo). Run EXPLAIN and add indexes in phpMyAdmin from your control panel. The database server is shared by many accounts, so server settings and the slow query log are managed by us. Port 3306 is closed from outside, so a desktop client such as MySQL Workbench needs an SSH tunnel. Jailed SSH access is available on every shared hosting plan (cPanel, DirectAdmin, Webuzo). It is off by default; ask support to enable it for your account. See how to connect to the MySQL database.
VPS. A Domain India VPS is self-managed with root access, so the server settings in the full guide are yours to tune.
How do I know if a MySQL query uses an index?
Run EXPLAIN in front of the query. The key column names the index MySQL chose; NULL means no index was used, and type ALL means MySQL read the whole table.
Which columns should I index?
The columns used in WHERE, JOIN and ORDER BY in your slowest and most frequent queries. For a query that filters on one column and sorts on another, one composite index with the equality column first and the sort column last usually works best.
Is SELECT * really slower?
It reads and sends every column, including long text and blob columns you may not display, and it stops MySQL from answering from the index alone. Listing only the columns you need is lighter and safer when the table changes.
Should I enable the MySQL query cache?
No. MySQL 8.0 removed the query cache, and MariaDB disables it by default because it slows busy servers. Use page caching for websites and an application cache on a server you manage.
Can I turn on the slow query log on shared hosting?
No. The database server on shared hosting is shared by many accounts, so its settings and logs are managed for everyone. You can still time and fix queries with EXPLAIN in phpMyAdmin.
Ready to make your database faster? Read the full MySQL performance optimization guide, check my website is slow, or compare VPS plans if you need full control of MySQL.
Send us the slow page, the query if you have it and when it happens, and we will help you find where the time goes.
Open a support ticket