MySQL Helpful Queries

BufferPool.sql

-- In absence of sys schema
SELECT table_name,
       ROUND(SUM(IF(compressed_size = 0, 16384, compressed_size)) / 1073741824, 2)  AS allocated_GB,
       ROUND(SUM(data_size) / 1073741824, 2)  AS data_GB,
       COUNT(page_number) AS pages,
       COUNT(IF(is_hashed = 'YES', 1, NULL)) AS pages_hashed,
       COUNT(IF(is_old = 'YES', 1, NULL)) AS pages_old,
       ROUND(SUM(number_records)/COUNT(DISTINCT index_name)) AS rows_cached 
 FROM information_schema.innodb_buffer_page
 WHERE table_name = "`db_name`.`table_name`"
 GROUP BY table_name;
                                       
-- with sys schema
 select * from innodb_buffer_stats_by_table where object_schema='schema_name' and object_name = 'table_name';

QueryPattern.sql

SELECT digest, count_star, first_seen, last_seen, sum_rows_sent, sum_rows_examined, avg_timer_wait/1000000000 
FROM performance_schema.events_statements_summary_by_digest 
WHERE digest_text LIKE '%table_name%INNER JOIN%' 
ORDER BY count_star DESC;