Why Your MySQL Queries Slow Down in Production and How to Fix Them

Common Pitfalls in Production

Even a query that runs in 10 ms locally can take 1 s after deployment. Typical issues include:

Indexes exist but queries remain slow.

Increasing the Go connection pool overloads the database.

A simple WHERE clause triggers a full table scan.

The root cause is often a lack of understanding of how MySQL executes queries, covering index paths, optimizer behavior, and Go connection‑pool dynamics.

1. Index Optimization: Reduce I/O, Not Just Add Indexes

Goal: Minimize back‑table lookups and random I/O. SELECT * FROM orders WHERE user_id = 1001; Even with an index, this can be slow because MySQL performs a back‑table lookup:

secondary index → primary key → data page (random I/O)

Correct approach: use a covering index that includes all needed columns.

SELECT id, user_id, amount FROM orders WHERE user_id = 1001;

If id, user_id and amount are in the index, MySQL returns results directly without back‑table access. A covering index therefore saves one B+‑tree jump and one random I/O.

2. Function Operations: Hidden Index Killers

Applying functions to indexed columns disables index usage.

SELECT * FROM orders WHERE DATE(created_at) = '2024-01-01';

Because the function breaks the index’s ordered property, the optimizer cannot use the index.

Solution: create a generated column and index it.

ALTER TABLE orders
  ADD COLUMN order_date DATE GENERATED ALWAYS AS (DATE(created_at)) VIRTUAL;
CREATE INDEX idx_order_date ON orders(order_date);

Now the query can use the index efficiently.

3. Index Condition Pushdown (ICP)

No ICP: Scan index → full back‑table → filter.

With ICP: Scan index → filter → back‑table (if needed).

Example: WHERE name LIKE '张%' AND age = 25 MySQL filters age during the index scan. Verify with: EXPLAIN SELECT ... Look for the Using index condition flag, which indicates ICP is active and reduces back‑table calls.

4. Slow Query Core: Wrong Execution Plan

Most slow queries stem from the optimizer choosing a sub‑optimal plan. Focus on three EXPLAIN fields:

type (access method): ALL means full table scan.

rows: Estimated rows examined; keep this low.

Extra: Flags such as Using index (covering index), Using index condition (ICP), Using filesort (expensive sort), Using temporary (temporary table creation).

5. JOIN Performance

Driving table size dominates JOIN cost. Avoid letting a large table drive a small filtered table.

SELECT * FROM orders o
JOIN users u ON o.user_id = u.id
WHERE u.status = 'active';

If orders has 1 M rows and users filters to 10 k, the join performs 1 M matches. Prefer the filtered table as the driver or force order with STRAIGHT_JOIN when the optimizer’s choice is clearly wrong.

6. Go + MySQL Pitfalls

Implicit Type Conversion

Mismatch between Go types and column types forces MySQL to convert values, breaking index usage.

-- Wrong
db.Where("user_id = ?", 123) // user_id is VARCHAR

MySQL converts the integer to a string, preventing index use.

-- Correct
db.Where("user_id = ?", "123")

Connection Pool Size

Oversized pools cause lock contention, increased context switches, and DB overload. Monitor with: db.Stats().WaitCount If WaitCount rises, optimize slow queries instead of adding connections.

Context Timeout Misunderstanding

Timeouts abort the client but do not guarantee MySQL stops execution immediately.

Proper three‑layer control:

Go context with deadline.

MySQL max_execution_time setting.

Continuous slow‑query optimization.

Timeouts are a safety net, not a performance fix.

Conclusion: Underlying Logic of SQL Optimization

Three fundamental goals:

Reduce I/O.

Lower scanned rows.

Avoid ineffective calculations.

Key takeaway: "Make MySQL do less work, not just work faster."

Production Self‑Check Checklist

SQL Checklist

Avoid SELECT *.

Use covering indexes.

Eliminate implicit type conversions.

Ensure queries hit the leftmost prefix of composite indexes.

Watch for filesort or temporary in EXPLAIN.

Go Checklist

Match Go types with DB column types.

Prefer QueryContext over plain queries.

Always close Rows.

Monitor connection‑pool health.

System Checklist

Analyze slow queries regularly.

Load‑test connection pools.

Implement timeout controls.

Set up alerting for performance anomalies.

Understanding problems across SQL, execution plan, storage engine, and application layers helps you advance from troubleshooting to proactive performance engineering.