Database performance tips

Many 504 errors and slow PHP response times are related to slow MySQL queries. It's your job as the responsible developer to make sure no insane MySQL queries are executed with your environment. When using a framework or CMS you might not write MySQL queries directly. Still you need to take that the requests to the database are executed without waste.

# Consider real usage scenarios

Most commonly database problems are not becoming visible in development when there are just a couple of rows in the tables. Also consider that a local machine might have more memory and CPU available, than the app here hosted on fortrabbit. In addition, consider that there might be more parallel requests to the app ongoing.

# Use profiling

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

# Framework and CMS plugins

• Laravel Debugbar
• Yii Debug Toolbar

# External services

APM (Application Performance Monitoring) is a whole industry of it's own. In professional environments, deep insights into code performance is crucial. Such tools will also provide stack tracing and issue spotting for database queries. Not all of them are supported with fortrabbit.

• Blackfire - integration guide
• New Relic - integration guide
• Tideways
• Laravel Nightwatch
• Flare by Spatie
• Datadog

Here are some ideas how you can help yourself without fancy tooling:

# Monitor live load with SHOW PROCESSLIST

Sometimes you need a snapshot of what the database is doing right now. Inside the MySQL shell you can run:

SHOW FULL PROCESSLIST;
sql

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 command requires the PROCESS privilege; managed fortrabbit environments include it for the application user. If you do not see full text results, use SHOW PROCESSLIST; together with SET GLOBAL log_output = 'TABLE'; or a slow query log for additional insight.

# 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;
sql

• 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;
sql

Watch the rows column for each table. If the joined table customers shows ALL, MySQL scans every customer regardless of the join condition—time to add an index on customers.id or rewrite the join.

# 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.

# Split write-tables & read-tables

Try to use separate write-intensive and read-intensive tables. Whenever you write on a table, you invalidate the query cache. For instance: if you have a read-intensive user table, and want to log their latest activity, use an additional table for this.

# Use Smart indexing

First off: Use indexes. But which? Look at your queries. Log your queries, run them through EXPLAIN and build your (multi-column) indexes based on the EXPLAIN results. Most importantly: repeat this from time to time, because things change and different data-size proportions might benefit from different indexes.

# Avoid JOINs

JOINs are easily used with modern ORMs and look like a good idea at first glance (and in certain situations they are!). However, when your App grows, they are many times the reason why your database performance decreases hugely. Why? Because there is a major difference between a join between 100 x 100 rows and 10,000 x 100,000 rows: the former fits in the memory, the latter not.

# 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. See Section 13.7.3.1, "ANALYZE TABLE" Statement.
• 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;
See Section 8.9.4, "Index Hints".
sql