Home

Wednesday, July 28, 2021

Percentage of Full Table Scans on MYSQL

Below is a useful script I use to determine what percentage of my queries in MYSQL are performing full table scans.  Limiting full table scans is a good place to start on improving database performance.


SELECT VARIABLE_VALUE INTO @Handler_read_rnd_next
FROM GLOBAL_STATUS 
WHERE variable_name ='Handler_read_rnd_next';
SELECT VARIABLE_VALUE INTO @Handler_read_rnd
FROM GLOBAL_STATUS 
WHERE variable_name ='Handler_read_rnd';
SELECT VARIABLE_VALUE INTO @Handler_read_first
FROM GLOBAL_STATUS 
WHERE variable_name ='Handler_read_first';
SELECT VARIABLE_VALUE INTO @Handler_read_next
FROM GLOBAL_STATUS 
WHERE variable_name ='Handler_read_next';
SELECT VARIABLE_VALUE INTO @Handler_read_key
FROM GLOBAL_STATUS 
WHERE variable_name ='Handler_read_key';
SELECT VARIABLE_VALUE INTO @Handler_read_prev
FROM GLOBAL_STATUS 
WHERE variable_name ='Handler_read_prev';


select ((@Handler_read_rnd_next + @Handler_read_rnd) / (@Handler_read_rnd_next + @Handler_read_rnd + @Handler_read_first + @Handler_read_next + @Handler_read_key + @Handler_read_prev))*100 as PercentFullTableScans;



No comments:

Post a Comment

Percentage of Full Table Scans on MYSQL

Below is a useful script I use to determine what percentage of my queries in MYSQL are performing full table scans.  Limiting full table sca...