Find Slow Queries in MySQL

slow-queries.sql

-- Some relevant tables:
use sys;
show tables like 'statements_with%'; -- and views
-- Results: statements_with_{errors_or_warnings,full_table_scans,runtimes_in_95th_percentile,sorting,temp_tables}

-- Raw (ie not human readable) views in sys start with x$, but are necessary for sorting

-- Slow queries
SELECT schema_name, total_latency, exec_count, query_sample_text
FROM sys.x$satements_with_runtimes_in_95th_percentile
JOIN performance_schema.events_statements_summary_by_digest ON digest
WHERE schema_name IS NOT 'sys'
AND   schema_name IS NOT 'performance_schema'
ORDER BY (total_latency/exec_count) DESC

-- Or try running an explain on em:
EXPLAIN (SELECT query_sample_test FROM above LIMIT 1)

-- Sequential Scans
SELECT schema_name, sum_rows_examined, total_latency, exec_count, query_sample_text
FROM sys.x$satements_with_full_table_scans
JOIN performance_schema.events_statements_summary_by_digest ON digest
WHERE schema_name IS NOT 'sys'
AND   schema_name IS NOT 'performance_schema'
ORDER BY (total_latency/exec_count) DESC