Database performance tips

Many 504 errors and slow PHP response times are related to slow MySQL queries. A framework or CMS writes most queries for the developer, but the developer still decides how many run and which indexes exist.

# Consider real usage scenarios

Database problems rarely show in development, with a few rows per table. A local machine may also have more memory and CPU than the MySQL plan, and production has parallel requests.

# Query and index metrics

Query and index charts for MySQL are in the works and not in the dashboard yet. The MySQL metrics show the database storage. Patterns to look for in a query log or a profiler:

• One query that runs thousands of times per hour often points to an N+1 problem: a loop that loads one row per iteration.
• A high average duration points to a missing index, see EXPLAIN below.
• Indexes that grow faster than the data point to duplicates, or to indexes no query uses.

# Use profiling

There are a couple of tools you can use to identify and debug slow queries:

# Framework and CMS plugins

• Laravel Debugbar
• Yii Debug Toolbar, which also runs inside Craft CMS, see Craft CMS database queries

# External services

APM (application performance monitoring) tools trace requests down to the database queries. fortrabbit has integration guides for two of them:

• Blackfire
• New Relic

Without extra tools, MySQL itself shows a lot:

# Live queries with SHOW PROCESSLIST

For a snapshot of what the database is doing right now, run this in a MySQL shell:

SHOW FULL PROCESSLIST;

This lists currently executing threads including the user, host, database, command type, runtime, and the full query text. Look for rows where Command is Query and Time keeps climbing, or where State mentions "Waiting for table lock" or "Copying to tmp table". Those usually point to problematic statements.

1. Connect to the MySQL shell of your environment.
2. Run SHOW FULL PROCESSLIST; a few times in succession to see patterns.
3. Copy suspicious queries and run them through EXPLAIN to find missing indexes or bad joins.
4. If zombie threads stay around, consider killing them with KILL <Id>; (but always fix the underlying query afterwards).

The list shows the connections of the database user, which covers every connection of the app.

# Use EXPLAIN to inspect query plans

EXPLAIN shows you which columns and which of their indices (if any) are used. It also points out if inefficient strategies, such as "temporary tables" or "filesort" are applied.

EXPLAIN SELECT ... shows how MySQL intends to execute a statement. The output highlights which indexes will be used, how many rows are inspected, join order, and whether temporary tables or filesorts are involved. Running an EXPLAIN before optimizing a query gives you a baseline; running it afterwards lets you confirm improvements.

# Basic example

EXPLAIN SELECT id, email
FROM customers
WHERE country = 'DE'
ORDER BY created_at DESC
LIMIT 25;

• type: The access method. ref or range is usually good; ALL signals a full scan.
• key: Index chosen for this table. NULL means no index is used.
• rows: Estimated rows to inspect. Large numbers hint at inefficient filters.
• Extra: Notes such as Using filesort or Using temporary that point to extra work.

# Highlighting join issues

EXPLAIN SELECT o.id, c.email
FROM orders o
JOIN customers c ON c.id = o.customer_id
WHERE o.status = 'open'
  AND o.created_at >= NOW() - INTERVAL 30 DAY;

Watch the rows column for each table. If orders shows ALL, MySQL reads every order to find the open ones. An index on orders (status, created_at) lets it read only those; customers.id is the primary key and indexed.

# Re-running after index changes

After adding or adjusting an index, rerun EXPLAIN. You should observe:

• key now referencing your new index
• lower rows estimates
• Extra dropping Using temporary or Using filesort

If the improvements do not show up, ensure statistics are current (ANALYZE TABLE) and that the query matches the index column order.

# Index what the queries use

Log the slow queries, run them through EXPLAIN and build indexes, often multi-column ones, for the columns in WHERE, JOIN and ORDER BY. Repeat this from time to time: as the data grows, other indexes become worth it.

JOINs are fine with indexes on the join columns. Without them, a join between two large tables reads far more rows than fit in memory. Check every join with EXPLAIN once the tables grow.

# Avoid full table scans

The output from EXPLAIN shows ALL in the type column when MySQL uses a full table scan to resolve a query. This usually happens under the following conditions:

• tables with few rows (index lookup not worth it)
• there is no ON or WHERE conditions for indexed columns
• constant values in columns spanning large portion of rows
• low cardinality index (many similar values in column)

For small tables, a table scan often is appropriate and the performance impact is negligible. For large tables, try the following techniques to avoid having the optimizer incorrectly choose a table scan:

• Use ANALYZE TABLE tbl_name to update the key distributions for the scanned table.
• Use FORCE INDEX for the scanned table to tell MySQL that table scans are very expensive compared to using the given index:

SELECT * FROM t1, t2 FORCE INDEX (index_for_column)
WHERE t1.col_name=t2.col_name;