MySQL performance diagnostics

MySQL Index / Query Plan Helper

Analyse a MySQL or MariaDB SELECT query using EXPLAIN and identify full table scans, inefficient joins, missing indexes and other performance problems.

Query plan analysis

Enter your database and query details

Connect to the selected database and analyse the supplied SELECT query using the database query optimiser.

EXPLAIN analysis

Analysing query plan...

IP address or hostname Database server

Port number Normally 3306

Database name Database containing the query tables

Database username SELECT and EXPLAIN access

Database password Password for the database user

SQL query to analyse SELECT queries only

The tool uses EXPLAIN to inspect how the database plans to execute the query. The supplied SELECT query is not intended to modify database records.

Submit only SELECT queries. Do not enter passwords, private customer data or SQL containing INSERT, UPDATE, DELETE, DROP, ALTER or other data-changing commands.

What this tool checks

Tables used by the query
Indexes selected by MySQL
Full table scans
Estimated rows examined
Join order and join type
Temporary tables and file sorting
Possible missing indexes

Common EXPLAIN types

const
MySQL expects to locate a single matching row.

eq_ref
An efficient indexed lookup is performed for each row from the previous table.

ref
An index is used to find one or more matching rows.

range
MySQL scans a selected range of an index.

ALL
A full table scan is performed and may need further investigation.

Performance warnings

Using filesort
MySQL may need an additional sorting operation for an ORDER BY or GROUP BY clause.

Using temporary
A temporary table may be created while processing the query.

No possible keys
MySQL cannot identify a suitable index for the table conditions.

High row estimate
The database expects to examine a large number of rows to produce the result.

Index guidance

Index columns commonly used in WHERE, JOIN, ORDER BY and GROUP BY clauses.

Composite indexes should normally place the most useful filtering columns first.

Avoid adding duplicate or unused indexes because they increase storage and can slow INSERT and UPDATE operations.

Test index changes on a recent backup before applying them to a production database.

MySQL Index / Query Plan Helper

Bell Online / MixStream

Download Unavailable