How To Fix Slow MYSQL Queries

Finding the Slowness

The chi of fixing slow MYSQL queries. Some of my best tips are here. Ready to dig in? Let’s get started.

Photo by Rubaitul Azad on Unsplash

I always start with the slow query log. You can’t fix what I cannot see. It’s like a security camera in a bank. It records exactly what goes wrong. I edit the MySQL configuration file. I set slow_query_log = 1. I set long_query_time = 1. Sometimes I set it to 0.1 seconds for busy systems. This logs every slow query to a text file.

I read that file. I use a command line tool called mysqldumpslow to group similar queries. I look for the queries that happen the most. I look for the queries that take the longest. You find the runway with the biggest traffic jam first. You fix that one.

The Execution Plan

I type EXPLAIN before my SELECT statement. This is my map. MySQL tells me exactly how it plans to execute the query. Think of a GPS. EXPLAIN gives me the exact route the database takes. I read specific columns in the output to spot bad routes.

I look at the type column. If I see ALL, I know I have a major problem. This means a full table scan. MySQL reads every single row in the table. If a table has ten million rows, it reads ten million rows. This kills speed. It is terrible. I want to see ref or eq_ref.

I look at the key column. This tells me which index MySQL actually picked. If it says NULL, MySQL ignored my indexes completely.

I look at the rows column. This is a guess. MySQL estimates how many rows it must examine to return the result. I want this number to be small. Very small.

I watch out for specific warnings in the Extra column. I look for Using filesort. I look for Using temporary. These mean MySQL does extra work after grabbing the data. It writes data to disk just to sort it. Disk is slow.

Making Indexes Work

Indexes speed up lookups. They act like the index at the back of a cookbook. You want a chicken recipe. You do not read the whole book page by page. You go to the index. You find “chicken”. You jump to page 45.

MySQL uses B-trees for most indexes. A B-tree keeps data sorted. It allows fast lookups. A B-tree is a balanced tree data structure. At the very top is a root node. This node holds ranges of values. It points to branch nodes. The branch nodes hold smaller ranges. They point to leaf nodes at the bottom.

The leaf nodes contain the actual index values. In InnoDB, the primary key leaf nodes contain the entire row of data. This is a clustered index. Every table has one. Secondary index leaf nodes contain the indexed column value. They also contain a pointer. This pointer is the primary key value.

This requires a double lookup. You search the secondary B-tree for a last name. You hit the leaf node. You extract the primary key. You search the primary B-tree for that primary key. You hit the clustered leaf node. You extract the full row. Millions of these traversals take time.

The Leftmost Prefix Rule

I create composite indexes. These are indexes on multiple columns. Order matters heavily. If I index (last_name, first_name), MySQL uses this index to find “Smith”. It uses it to find “Smith, John”. It cannot use it to find just “John”. The database reads left to right.

If I query WHERE first_name = ‘John’, the index does nothing. The query is slow. I create a separate index for first_name if I query it alone frequently.

Covering Indexes

This is my favorite trick. A covering index includes all the columns a query needs.

I write SELECT first_name, last_name FROM users WHERE last_name = ‘Smith’.

If my index is just on last_name, MySQL finds the row in the index. Then it jumps to the actual table data on disk to get first_name. This extra jump takes time.

If I make an index on (last_name, first_name), MySQL gets everything it needs directly from the index. It never touches the main table data. It skips the second B-tree search entirely. The query finishes instantly.

Fixing Bad Queries

Sometimes the index is fine. The query itself is trash. I rewrite bad queries every week.

Stop Asking for Everything

I never write SELECT *. It is a lazy habit. I specify exact columns. SELECT id, name, email.

Fetching columns I do not need wastes memory. It wastes network bandwidth. The database does extra work. The application server chokes on useless data. It is like ordering the entire menu at a restaurant when you only want a burger. Just ask for the burger.

The N+1 Problem

This happens often with Object-Relational Mappers. I query fifty users. Then the code loops. It runs a new query to get orders for each user. That is fifty-one queries. Each query has network latency. Each query has parsing overhead. Each query takes time.

I fix this by asking for everything at once. I use an IN clause.

SELECT * FROM orders WHERE user_id IN (1, 2, 3…).

One query replaces fifty. It changes everything.

Hiding Indexed Columns in Functions

I see this mistake constantly. Someone wants sales from 2023. They write WHERE YEAR(created_at) = 2023.

MySQL evaluates the YEAR() function on every single row before it can compare the result. It cannot use the index on created_at. The index is useless now.

I rewrite it. I move the math away from the column. I write WHERE created_at >= ‘2023–01–01’ AND created_at < ‘2024–01–01’. MySQL sees the raw column. It uses the index. The query runs fast.

Text Search and Wildcards

I search for text using LIKE. If I write WHERE username LIKE ‘admin%’, MySQL uses the index. It knows where “admin” starts in the B-tree.

If I write WHERE username LIKE ‘%admin%’, the index fails. The wildcard is at the front. MySQL does not know where the string starts. It resorts to a full table scan.

If I need deep text search, I do not use LIKE. I use a dedicated tool. I use Elasticsearch or Sphinx. MySQL is a relational database. It is bad at full-text search.

Managing Sorting and Grouping

Sorting data is hard work. When I write ORDER BY, I want MySQL to read the data already sorted from an index.

If the index is (status, created_at), and I query WHERE status = ‘active’ ORDER BY created_at, MySQL uses the index for both. It filters and sorts simultaneously.

If my ORDER BY does not match the index, MySQL does a filesort. It reads the data. It puts it in memory. It runs a quicksort algorithm. If the data is too big for memory, it writes chunks to disk. This destroys speed. I avoid filesorts by aligning my indexes with my sort clauses.

Memory and Configuration

I tune the server. Hardware matters. MySQL settings matter more.

The InnoDB Buffer Pool

This is the heart of MySQL memory. InnoDB stores cached data and indexes here. If data is in the buffer pool, MySQL reads it from RAM. RAM is incredibly fast. If data is not in the buffer pool, MySQL reads it from the hard drive. Hard drives are slow.

I set innodb_buffer_pool_size to roughly 70 percent of my total server RAM. If the server has 64 gigabytes of RAM, I give the buffer pool 45 gigabytes. I leave the rest for the operating system. I leave the rest for network handshakes. I leave the rest for client connections.

I check the buffer pool hit rate. I run SHOW ENGINE INNODB STATUS. I want the hit rate above 99 percent. If it drops, the server needs more RAM.

Temporary Tables in Memory

Some complex queries require temporary tables. MySQL tries to put them in RAM. It uses the tmp_table_size and max_heap_table_size settings.

If the temporary table gets larger than these values, MySQL writes it to disk. Disk-based temporary tables are slow. I increase these values slightly on servers running heavy analytical queries. But I am careful. If I make them too big, the server runs out of memory. It crashes.

Connection Management

Opening a connection to MySQL takes time. The server checks credentials. It allocates memory. It sets up threads. It handles network handshakes.

If an application opens a new connection for every single query, it wastes a massive amount of time. It is like buying a new car every time you drive to the store.

I always use connection pooling. The application opens a pool of connections once. It reuses them. When a query finishes, the connection goes back to the pool. This keeps latency low. It stops the database from thrashing.

PHP historically lacked good connection pooling. Every HTTP request opened a new database connection. It closed it when the request died. I ran high-traffic PHP applications. The database spent half its CPU time doing nothing but shaking hands with new PHP workers.

I fixed it with ProxySQL. I put ProxySQL between my application and the database. The application connects to ProxySQL instantly. ProxySQL holds a steady pool of persistent connections open to the real database. It forwards the queries. The database CPU usage drops instantly.

Locking and Contention

Sometimes a query is fast but it waits. It is stuck behind another query. InnoDB uses row-level locking.

If one transaction updates a row, it holds a lock. If my read query needs that exact row in a specific isolation level, it waits. The query sleeps for five seconds waiting for the lock to release.

I check SHOW PROCESSLIST. I look for queries in the Lock wait state.

I keep transactions short. I do not make API calls to other services inside a database transaction. I read data. I update data. I commit immediately. I release locks quickly.

Pagination Problems

I build web applications. I show pages of results. I use LIMIT and OFFSET.

SELECT * FROM comments LIMIT 10 OFFSET 500000.

This is a disaster. MySQL does not just jump to row 500,000. It reads 500,010 rows. It throws away the first half million. It returns the last ten. This takes forever on large tables.

I fix this with cursor-based pagination. I remember the last ID I saw.

SELECT * FROM comments WHERE id > 500000 LIMIT 10.

MySQL uses the primary key index. It jumps directly to the correct spot. The query is fast. It stays fast as the table grows.

Reviewing Schema Choices

Data types dictate speed. I use the smallest possible data type.

If a column holds values between 1 and 100, I use TINYINT. I do not use INT or BIGINT. Smaller types use less disk space. They use less memory in the buffer pool. They make indexes smaller. They make indexes faster to read.

I avoid VARCHAR when CHAR works. If I store a 32-character hash, I use CHAR(32). Fixed-length columns speed up internal calculations.

I avoid NULL values if I can. I set NOT NULL on most columns. NULL complicates index storage. It complicates comparison logic inside the engine. It adds overhead. I prefer default values like zero or empty strings.

Joins and Subqueries

Joining tables is normal. Doing it badly causes outages.

I make sure I have indexes on both sides of a JOIN. If I join users and orders on user_id, both tables need an index on that column.

Subqueries trip me up sometimes. Older versions of MySQL handle IN subqueries terribly.

SELECT * FROM users WHERE id IN (SELECT user_id FROM inactive_accounts).

Historically, MySQL ran the outer query first. It executed the inner query for every single row. I rewrite these into JOIN statements.

SELECT users.* FROM users JOIN inactive_accounts ON users.id = inactive_accounts.user_id.

The MySQL query planner handles joins much better. It builds a good execution plan.

Dropping Old Indexes

Indexes are not free. They speed up SELECT queries. They slow down INSERT, UPDATE, and DELETE queries.

Every time a row changes, MySQL updates the table data. Then it updates every single index attached to that table. If a table has twenty indexes, an insert takes a long time.

I audit my indexes every few months. I use the sys schema to find unused indexes. I read the official MySQL documentation on how to query index usage statistics safely. I drop the unused ones. Less baggage means faster writes.

Deep Dive into Execution Plans

The standard EXPLAIN output is basic. I need more details. I use EXPLAIN FORMAT=JSON.

This changes the output. It gives me a massive JSON object. It reveals the exact cost estimates the planner calculated. I see how much time MySQL thinks it spends reading disk versus evaluating conditions.

I look at query_cost. This is a numeric value. It helps me compare two different versions of a query. If query A has a cost of 500 and query B has a cost of 15, query B wins.

Sometimes MySQL picks a weird index. I want to know why. I turn on the execution trace.

SET optimizer_trace=”enabled=on”;

I run my query. Then I query the information_schema.optimizer_trace table. It contains a massive JSON dump of the planner’s brain. I read through it. I see every index it considered. I see the exact mathematical reason it rejected my preferred index.

Updating Index Statistics

MySQL relies on statistics to make decisions. It samples the data in the tables to guess how many distinct values exist in a column. This tells it how useful an index is.

If I delete ten million rows, the statistics get stale. MySQL thinks the table is still huge. It makes bad choices.

I fix this manually. I run ANALYZE TABLE my_table_name.

This forces InnoDB to resample the data. It recalculates the index cardinality. Suddenly, the query planner wakes up. It picks the correct index again. The query speeds up instantly. I do this during low-traffic hours. It requires a read lock.

The Perils of OR Queries

I hate using OR in a WHERE clause. It confuses the database.

SELECT * FROM users WHERE status = ‘active’ OR role = ‘admin’.

If I have an index on status and a separate index on role, MySQL has to work hard. It will sometimes use an index merge. It reads from both indexes. It unions the results in memory. This is okay, but not fast. Often, it just gives up and scans the whole table.

I fix this with UNION. I split the query into two simple pieces.

• SELECT * FROM users WHERE status = ‘active’
• UNION
• SELECT * FROM users WHERE role = ‘admin’

Each piece uses its specific index perfectly. The results are merged. It is much faster.

Gap Locks and Deadlocks

Slow queries are tied to locking. InnoDB uses next-key locks to prevent phantom reads.

If I run an UPDATE on a range of rows, MySQL locks the exact rows. It also locks the gaps between the rows.

UPDATE products SET stock = 0 WHERE category_id = 5.

If another transaction tries to insert a new product with category_id = 5, it hangs. It waits for the first transaction to finish. The lock blocks it. This creates a slow experience for the user making the insert.

I reduce gap locking by changing my transaction isolation level. The default is REPEATABLE READ. I switch it to READ COMMITTED. This disables gap locks for most statements. Inserts stop blocking. Concurrency goes up. I just handle non-repeatable reads in my application logic.

Denormalization

Relational databases prefer normalized data. You split data into many tables to avoid duplication.

Sometimes normalization kills speed. I join five tables just to display a user profile. It takes too long.

I break the rules. I denormalize. I duplicate data on purpose.

If I need the user’s total order count on their profile, I do not run COUNT(*) on the orders table every time. I add a total_orders column to the users table. I update it when a new order happens. Reading the profile takes one simple query. The read is fast. The write is slightly slower. I accept that trade. Websites read data vastly more often than they write it.

Prepared Statements

I use prepared statements in my application code. This is good for security. It stops SQL injection. It also helps speed.

When I send a raw SQL query, MySQL parses the string. It validates the syntax. It generates an execution plan. It runs the query.

With a prepared statement, I send the structure first.

SELECT * FROM users WHERE email = ?.

MySQL parses it once. It builds the plan once. Then I just send the variables over and over. The database skips the parsing step on subsequent calls. This saves CPU cycles. It reduces overhead on busy systems.

Character Sets and Collations

Mixing character sets ruins performance.

I have a users table using utf8mb4. I join it to an orders table using latin1.

SELECT * FROM users u JOIN orders o ON u.email = o.user_email.

MySQL cannot compare these directly. It converts the strings in memory for every single row before it can check for a match. The index on user_email is ignored. The join is horribly slow.

I enforce strict consistency. Every table must use the exact same character set. Every table must use the exact same collation. I prefer utf8mb4_0900_ai_ci for modern apps. It handles emojis correctly.

The Death of the Query Cache

I see older tutorials mention the MySQL Query Cache. They say to turn it on. I ignore them completely. MySQL removed the query cache in version 8.0. It was a bottleneck.

When a table changed, even slightly, MySQL had to invalidate every single cached query related to that table. On a system with heavy writes, the cache caused massive lock contention. The server spent more time managing the cache than serving data.

I rely on external caches now. I use Redis. I use Memcached. The application checks Redis first. If the data is there, it skips MySQL entirely. This takes the load off the database.

Managing Threads

MySQL assigns one thread to every client connection. If I have five thousand active connections, I have five thousand threads fighting for CPU time. Context switching destroys performance. The CPU spends more time swapping threads than doing actual math.

I fix this by limiting connections at the application level.

If I use MySQL Enterprise or Percona Server, I enable the Thread Pool. This feature groups connections together. It limits the number of actively running queries to match the number of CPU cores. The other queries wait in a queue. This sounds slower, but it is faster. The CPU finishes tasks sequentially without thrashing.

Writing Data Fast

Slow inserts affect everything else. Table locks block reads. When I insert thousands of rows, I do not write thousands of INSERT statements. I write one massive INSERT statement.

INSERT INTO logs (level, message) VALUES (‘info’, ‘start’), (‘error’, ‘crash’), (‘info’, ‘stop’).

This bulk insert reduces parsing time. It reduces index updates. It writes to the transaction log in one big chunk instead of thousands of small chunks. It speeds up writes dramatically.

If I need to import millions of rows, I do not use INSERT at all. I use LOAD DATA INFILE. It bypasses the normal SQL parser completely. It dumps the data straight into the storage engine. It is the fastest way to get data into MySQL.

The Undo Log and Long Transactions

I keep an eye on long-running transactions. Even if they are just reading data.

InnoDB uses Multi-Version Concurrency Control. If a transaction starts reading at 1:00 PM, it must see the database exactly as it was at 1:00 PM. It does this even if other queries change data later. To do this, InnoDB keeps old versions of rows in the undo log.

If a transaction stays open for three hours, the undo log grows massively. It fills up the disk. It slows down every other query because the database navigates a maze of old row versions to figure out what data is currently valid.

I kill idle transactions. I ensure my code commits or rolls back immediately.

Regular Maintenance

Databases need cleaning. Fragmentation happens.

When I delete rows, MySQL does not shrink the data file. It leaves an empty hole in the page. If I delete heavily, my tables become full of holes. The disk file is huge. It contains very little actual data. When MySQL does a table scan, it reads all those empty holes. It reads useless disk blocks.

I rebuild the table. I run ALTER TABLE my_table_name ENGINE=InnoDB.

This creates a brand new, tightly packed version of the table. It rebuilds the indexes. It reclaims disk space. It speeds up table scans. I schedule this to run during maintenance windows.

I hope these help you better able to quickly debug and fix your MYSQL queries. Good luck!

Note: I use affiliate links in my articles.

Interested in affiliate marketing? Start here.

I create apps that help marketers.

*PromptQuik: Store, organize, tag and share any of your AI prompts. Create a free database of the best prompts you find online.*

*VidBooster: Deeper YouTube Analytics and bulk tools to help small channels grow faster.*

*AlertBarPro: Add eye-catching notification bars to your websites. Turn visitors into subscribers.*

*GetBadgered: Freelancers — Stop chasing deadbeat clients. Let Badger loose on them.*

*VidCommenter.com: The biggest growth channel you’re sleeping on. Free Beta.*

I share some great content on my YouTube channel that doesn’t work in written format.

Follow me on Medium.