When analyzing queries for performance issues, we want to look for inefficiencies in order to make our queries as optimized as possible. We therefore for things such as: Operations that are inefficient, proper indexing, reducing the number of rows that are processed. In order to carry out an analysis on these queries, we use an Execution Plan. In this blog post, I will show how to carry out an execution plan using MySQL, MariaDB, PostgreSQL, and MS SQL Server.

Examples

MySQL

In order to carry out an execution plan on MySQL, we use the EXPLAIN keyword, followed by the query that we want to analyze.

EXPLAIN SELECT * FROM users u
JOIN positions p on u.user_id = p.user_id
WHERE u.last_name = 'Ahmad';

Execution Statistics

EXPLAIN ANALYZE SELECT * FROM users u
JOIN positions p on u.user_id = p.user_id
WHERE u.last_name = 'Ahmad';

Index Usage Statistics

SELECT * FROM performance_schema.table_io_waits_summary_by_index_usage 
WHERE object_schema = 'your_database';

MariaDB

MariaDB will use the same process as the first example for MySQL above, but if we want extended information, we need to do the following:

EXPLAIN EXTENDED SELECT * FROM users u
JOIN positions p on u.user_id = p.user_id
WHERE u.last_name = 'Ahmad';
SHOW WARNINGS;

PostgreSQL

EXPLAIN SELECT * FROM users u
JOIN positions p on u.user_id = p.user_id
WHERE u.last_name = 'Ahmad';

MS SQL Server

SET SHOWPLAN_TEXT ON;
SELECT * FROM users u
JOIN positions p on u.user_id = p.user_id
WHERE u.last_name = 'Ahmad';
SET SHOWPLAN_TEXT OFF;

Using Detailed Analysis:

SET STATISTICS IO ON;
SET STATISTICS TIME ON;
SELECT * FROM users u
JOIN positions p on u.user_id = p.user_id
WHERE u.last_name = 'Ahmad';
SET STATISTICS IO OFF;
SET STATISTICS TIME OFF;

What to Look For

Your query is considered good if you have the following:

• Your joins are efficient.
• Indices are being used instead of table scans.
• Your row count is low.
• No sorting operations occur with data that is already indexed.

Warning signs that you need to take another look at your query:

• Joins on small datasets.
• Table scans are being done on tables that are large.
• Row count being processed is high.
• Temp tables are being created.