# Database performance tips

Source: https://docs.fortrabbit.com/dev/performance/database
Reviewed: 2026-07-06

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


Many [504 errors](/dev/http-status-codes/504) 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](/platform/objects/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 can 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 its 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](/integrations/apm/blackfire)
- New Relic - [integration guide](/integrations/apm/new-relic)
- 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:



```sql
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 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



```sql
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



```sql
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 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.
- Use `FORCE INDEX` for the scanned table to tell MySQL that table scans are very expensive compared to using the given index:



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