Skip to content

Instantly share code, notes, and snippets.

What would you like to do?
MySQL Helpful Queries
-- 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';
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;
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment