Apex Software Technologies
Database

MySQL Query Optimization: 10 Tips for Faster Databases

April 22, 2026
9 min read

The Hidden Cost of Bad Queries

When you build a PHP/MySQL application locally, everything feels lightning fast. You have a table with 10 records, and a SELECT * FROM users query executes in 0.001 seconds.

Then you deploy to production. A year passes. That table now has 5 million records. Suddenly, your application grinds to a halt. Pages take 8 seconds to load, server CPU usage spikes to 100%, and users abandon your site in frustration.

The culprit? Unoptimized MySQL queries.

In this deep dive, we will explore advanced strategies for optimizing MySQL queries to handle enterprise-level scale without melting your servers.

1. The Evils of SELECT *

The most common mistake junior developers make is using SELECT *.

When you use the asterisk, you are forcing the database to read and return every single column in the table, even if you only need the id and username.

Why it's bad:

  • Network IO: Sending unused data across the network from the database server to the web server wastes bandwidth.
  • Memory Consumption: Your PHP application has to allocate memory to store data it will never use.
  • Disk I/O: The database engine has to read more data from the physical disk.

The Fix: Always explicitly define the columns you need.

sql
-- BAD
SELECT * FROM users WHERE status = 'active';

-- GOOD
SELECT id, username, email FROM users WHERE status = 'active';

2. Master the EXPLAIN Statement

If a query is slow, you must use the EXPLAIN statement. EXPLAIN tells you exactly how MySQL is executing your query. It reveals whether MySQL is using an index, or if it is performing a dreaded "Full Table Scan".

Simply prepend the word EXPLAIN to your query:

sql
EXPLAIN SELECT id FROM orders WHERE user_id = 452;

Look at the type column in the result. If it says ALL, you have a massive problem—MySQL is scanning every single row in the table to find the match. If it says ref or eq_ref, MySQL is efficiently using an index.

3. Proper Indexing (The Silver Bullet)

Indexes are the single most important concept in database performance. An index works exactly like the index at the back of a textbook. Instead of reading the entire book page by page to find a topic, you look at the index, find the exact page number, and jump straight there.

When to create an index:

  • On columns frequently used in WHERE clauses.
  • On columns used in JOIN conditions (Foreign Keys).
  • On columns used in ORDER BY or GROUP BY clauses.
sql
-- Creating an index on the email column
CREATE INDEX idx_user_email ON users(email);

Warning: Do not index every column! Indexes speed up READ operations, but they slow down WRITE operations (INSERT, UPDATE, DELETE) because the database has to update the index every time the data changes.

4. Beware the N+1 Query Problem

This is an architectural issue common in PHP ORMs (like Eloquent or Doctrine), but it applies to raw SQL too.

Imagine you fetch 100 blog posts. Then, for each blog post, you run a separate query to fetch the author's name. That results in 1 query for the posts, plus 100 queries for the authors = 101 queries.

The Fix: Use JOIN or Eager Loading. By using a JOIN, you can fetch the posts and the author names in exactly 1 query.

sql
SELECT posts.title, users.username 
FROM posts 
INNER JOIN users ON posts.author_id = users.id;

5. Limit Your Results

If you are displaying a list of recent transactions to a user, they don't need to see all 50,000 of them at once. Always use the LIMIT clause to constrain the result set and implement pagination.

sql
SELECT amount, date FROM transactions WHERE user_id = 123 ORDER BY date DESC LIMIT 20;

6. Avoid Functions on Indexed Columns

If you have an index on the created_at column, wrapping that column in a SQL function completely destroys the index, forcing a full table scan.

sql
-- BAD: Forces a full table scan because the index can't be used on YEAR()
SELECT * FROM orders WHERE YEAR(created_at) = 2026;

-- GOOD: The index works perfectly
SELECT * FROM orders WHERE created_at >= '2026-01-01' AND created_at < '2027-01-01';

Conclusion

Query optimization is both an art and a science. It requires monitoring your production logs for slow queries, utilizing the EXPLAIN command, and strategically applying indexes. By eliminating SELECT *, fixing N+1 issues, and respecting your indexes, you can ensure your MySQL database remains blazing fast no matter how large your application scales.