Find slowest queries

from the Artful MySQL Tips List


These queries need performance_schema enabled.
SELECT 
  digest_text, count_star, first_seen, last_seen
  avg_timer_wait/1024/1024/1024/1024 as avg_wait_secs
FROM performance_schema.events_statements_summary_by_digest 
ORDER BY avg_timer_wait DESC
LIMIT 10;
A more advanced query for slow queries, needs MySQL 8.0 ...
SELECT 
  schema_name, 
  sys.format_time(total_latency) tot_lat,   
  exec_count, 
  sys.format_time(total_latency/exec_count) latency_per_call, 
  query_sample_text 
FROM sys.x$statements_with_runtimes_in_95th_percentile AS t1
JOIN performance_schema.events_statements_summary_by_digest AS t2 
  ON t2.digest=t1.digest 
WHERE schema_name NOT IN ('performance_schema', 'sys') 
ORDER BY (total_latency/exec_count) desc LIMIT 10; 
Find queries that require full table scans ...
SELECT 
  schema_name, sum_rows_examined, 
  (sum_rows_examined/exec_count) avg_rows_call,
  sys. format_time(total_latency) tot_lat, exec_count,
  sys. format_time(total_latency/exec_count) AS latency_per_call,
  query_sample_text 
FROM sys.x$statements_with_full_table_scans AS t1
JOIN performance_schema.events_statements_summary_by_digest AS t2 
  ON t2.digest=t1.digest 
WHERE schema_name NOT in ('performance_schema', 'sys') 
ORDER BY (total_latency/exec_count) desc ;
Find temp tables left behind by queries...
SELECT 
  schema_name, 
  sys.format_time(total_latency) tot_lat, exec_count, 
  sys.format_time(total_latency/exec_count) latency_per_call, 
  query_sample_text 
FROM sys.x$statements_with_temp_tables AS t1
JOIN  performance_schema.events_statements_summary_by_digest AS t2
  ON t2.digest=t1.digest 
WHERE schema_name NOT in ('performance_schema', 'sys') AND disk_tmp_tables=1
ORDER BY 2 desc,(total_latency/exec_count) desc; 
More info at https://lefred.be/content/mysql-8-0-if-i-should-optimize-only-one-query-on-my-application-which-one-should-it-be

Last updated 16 Aug 2019


Return to the Artful MySQL Tips page