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 |
![]() |