MySQL: Useful queries to debug and optimize query execution

list_status_counters.sql

-- This is useful to see counters that are updated while executing the query. This provides useful information
-- about number of times the DB executed operations over indices or tables. It also provides information about
-- the sorting process. More info about counters: https://fromdual.com/mysql-handler-read-status-variables

-- ****** When you have enough permissions to flush server status counters ******

FLUSH STATUS;

-- QUERY TO ANALYZE

SHOW SESSION STATUS WHERE Variable_name LIKE  '%handler%' OR Variable_name LIKE '%Sort%';


-- ****** When you cannot flush server status counters due to lack of permissions ******

SET @read_first_before := (select VARIABLE_VALUE from information_schema.SESSION_STATUS where VARIABLE_NAME = 'Handler_read_first');
SET @read_key_before := (select VARIABLE_VALUE from information_schema.SESSION_STATUS where VARIABLE_NAME = 'Handler_read_key');
SET @read_last_before := (select VARIABLE_VALUE from information_schema.SESSION_STATUS where VARIABLE_NAME = 'Handler_read_last');
SET @read_next_before := (select VARIABLE_VALUE from information_schema.SESSION_STATUS where VARIABLE_NAME = 'Handler_read_next');
SET @read_prev_before := (select VARIABLE_VALUE from information_schema.SESSION_STATUS where VARIABLE_NAME = 'Handler_read_prev');
SET @read_rnd_before := (select VARIABLE_VALUE from information_schema.SESSION_STATUS where VARIABLE_NAME = 'Handler_read_rnd');
SET @read_rnd_next_before := (select VARIABLE_VALUE from information_schema.SESSION_STATUS where VARIABLE_NAME = 'Handler_read_rnd_next');
SET @sort_merge_passes_before := (select VARIABLE_VALUE from information_schema.SESSION_STATUS where VARIABLE_NAME = 'Sort_merge_passes');
SET @sort_range_before := (select VARIABLE_VALUE from information_schema.SESSION_STATUS where VARIABLE_NAME = 'Sort_range');
SET @sort_scan_before := (select VARIABLE_VALUE from information_schema.SESSION_STATUS where VARIABLE_NAME = 'Sort_scan');
SET @sort_rows_before := (select VARIABLE_VALUE from information_schema.SESSION_STATUS where VARIABLE_NAME = 'Sort_rows');

-- QUERY TO ANALYZE

SET @read_first_after := (select VARIABLE_VALUE from information_schema.SESSION_STATUS where VARIABLE_NAME = 'Handler_read_first');
SET @read_key_after := (select VARIABLE_VALUE from information_schema.SESSION_STATUS where VARIABLE_NAME = 'Handler_read_key');
SET @read_last_after := (select VARIABLE_VALUE from information_schema.SESSION_STATUS where VARIABLE_NAME = 'Handler_read_last');
SET @read_next_after := (select VARIABLE_VALUE from information_schema.SESSION_STATUS where VARIABLE_NAME = 'Handler_read_next');
SET @read_prev_after := (select VARIABLE_VALUE from information_schema.SESSION_STATUS where VARIABLE_NAME = 'Handler_read_prev');
SET @read_rnd_after := (select VARIABLE_VALUE from information_schema.SESSION_STATUS where VARIABLE_NAME = 'Handler_read_rnd');
SET @read_rnd_next_after := (select VARIABLE_VALUE from information_schema.SESSION_STATUS where VARIABLE_NAME = 'Handler_read_rnd_next');
SET @sort_merge_passes_after := (select VARIABLE_VALUE from information_schema.SESSION_STATUS where VARIABLE_NAME = 'Sort_merge_passes');
SET @sort_range_after := (select VARIABLE_VALUE from information_schema.SESSION_STATUS where VARIABLE_NAME = 'Sort_range');
SET @sort_scan_after := (select VARIABLE_VALUE from information_schema.SESSION_STATUS where VARIABLE_NAME = 'Sort_scan');
SET @sort_rows_after := (select VARIABLE_VALUE from information_schema.SESSION_STATUS where VARIABLE_NAME = 'Sort_rows');

SELECT 
@read_first_after - @read_first_before AS read_first,
@read_key_after - @read_key_before AS read_key,
@read_last_after - @read_last_before AS read_last,
@read_next_after - @read_next_before AS read_next,
@read_prev_after - @read_prev_before AS read_prev,
@read_rnd_after - @read_rnd_before AS read_rnd,
@read_rnd_next_after - @read_rnd_next_before - 22 AS read_rnd_next,
@sort_merge_passes_after - @sort_merge_passes_before AS sort_merge_passes,
@sort_range_after - @sort_range_before AS sort_range,
@sort_scan_after - @sort_scan_before AS sort_scan,
@sort_rows_after - @sort_rows_before AS sort_rows;

profile_query_execution.sql

-- Template to profile MySQL queries
SET profiling = 1;

-- QUERY TO BE PROFILED

SHOW PROFILES;
SHOW PROFILE SOURCE FOR QUERY 3; -- Replace 3 with the number of your query as shown in SHOW PROFILES

trace_the_optimizer.sql

-- Trace the query optimizer to understand decisions being made

SET optimizer_trace="enabled=on";
SET optimizer_trace_max_mem_size=1000000;

EXPLAIN ; -- Add query to be analyzed here

SELECT * FROM information_schema.optimizer_trace;
SET optimizer_trace="enabled=off";