MySQL 8.4: Performance and maintenance

See a query's plan

Show how MySQL plans to run a query.

EXPLAIN SELECT * FROM users WHERE email = '[email protected]';

Run a query and see real timings

Execute the query and show the plan with actual row counts and times.

EXPLAIN ANALYZE SELECT * FROM users WHERE email = '[email protected]';

Caution: EXPLAIN ANALYZE really executes the statement, so don't point it at INSERT, UPDATE or DELETE outside a test database.

Create an index

Add an index to speed up lookups on a column.

CREATE INDEX idx_users_email ON users (email);

Refresh index statistics

Update the key distribution statistics the optimizer uses.

ANALYZE TABLE users;

See running queries

List the connections and the statements they are running.

SHOW FULL PROCESSLIST;

Check a server setting

Show the value of a system variable, such as the connection limit.

SHOW VARIABLES LIKE 'max_connections';

Count open connections

Show how many client connections are open right now.

SHOW STATUS LIKE 'Threads_connected';