MySQL – Index Statistics

Background

Wanted to cover how to review Index Statistics in MySQL.

Overview

The tables that we will be using are:

  1. mysql.innodb_table_stats
  2. mysql.innodb_index_stats

Code

 

Item Column Explanation Sample
Database mysql.innodb_index_stats.database_name Database sakila
table mysql.innodb_index_stats.table_name Table actor, film, inventory
Index mysql.innodb_index_stats.index_name Index Name PRIMARY, idx_fk_film_id, idx_store_id_film_id
Stat Name mysql.innodb_index_stats.stat_name Statistics Name n_diff_pfx01
n_diff_pfx02
n_diff_pfx03
Stat Columns mysql.innodb_index_stats.stat_description (
where stat_name=n_diff_pfxNN, the stat_description column shows a comma separated list of the index columns that are counted. )
Key Columns store_id
store_id,film_id
store_id,film_id,inventory_id
Stat Value mysql.innodb_index_stats.stat_value Number of Unique Values in index
Number of Pages Sampled mysql.innodb_index_stats.sample_size Number of pages sampled
Number of Pages Total mysql.innodb_index_stats.stat_value ( mysql.innodb_index_stats.stat_description = ‘Number of pages in the index’ ) Number of Pages total
Sample % mysql.innodb_index_stats.stat_value (stat.name like ‘n_diff_pfx%’ )
 / mysql.innodb_index_stats.stat_value ( stat.name = ‘size’ )
# of Rows mysql.innodb_table_stats.n_rows Number of records in table
Table Size (
INFORMATION_SCHEMA.TABLES.data_length
+ INFORMATION_SCHEMA.TABLES.index_length
)
Table Size from INFORMATION_SCHEMA. TABLES
(
mysql.innodb_table_stats.clustered_index_size
+ mysql.innodb_table_stats.sum_of_other_index_sizes
)
*
(
@@innodb_page_size
)
Table Size from mysql.innodb_table_stats.

Data is availed as Number of Pages.

Index Size (
mysql.innodb_index_stats.stat_value
* @@innodb_page_size
)where ( mysql.innodb_index_stats.stat_name = ‘size’ )
Index Size from mysql.innodb_index_stats.

Information logged is Number of pages.

Filter on stat_name = ‘size’.

Stats Update TS mysql.innodb_index_stats.last_update Last time statistics was updated
Page Size ( @@innodb_page_size / 1024 ) 16 KB

SQL


-- Create database dbUtility if it does not exist
CREATE database IF NOT EXISTS dbUtility;

-- use database dbUtility
use dbUtility;

-- Change delimiter from default of ; to //
delimiter //


DROP PROCEDURE IF EXISTS listIndexStatsInnoDB;
//

create procedure listIndexStatsInnoDB
(
      `database`   nvarchar(100) 
    , `table`      nvarchar(100)
    , `index`      nvarchar(100)
)
begin
    
    /*
     * database :- database
     * table    :- table
     * index    :- index
     */


	select
			  tblIS.database_name as 'database'
			
			, tblIS.table_name as 'table'
			
			, tblIS.index_name as 'index'
			
			, tblIS.stat_name as 'statName'
			
			, tblIS.stat_description as 'statColumns'		
			
			, tblIS.stat_value as 'statValue'
			
			, tblIS.sample_size as 'sampleSize'
			
			, tblISSP.stat_value as 'numberofPages'

			, truncate
			   (
			     case
			        when (tblISSP.stat_value > 0) 
			          then ( tblIS.sample_size * 100.00)
			            / 
			            nullif
			            (
			                tblISSP.stat_value
			               ,0 
			            )
			        else null
			     end
			     , 2
			   ) as '%Sample'
			   
			-- , tblISS.cardinality as 'cardinality'
	
			-- , tblIST.TABLE_ROWS as '#OfRows'
			, tblITS.n_rows as '#ofRows'

			-- using information.schema_tables
			, concat
			  (
			     round
			     (
			         (
			            ( 
			                tblIST.data_length 
			              + tblIST.index_length 
			            )
			            * 1 
			            /
			            (
			              1024 * 1024 
			            )
			         )   
			       , 2
			     )
			     ,' MB'
			  ) 'tableSizeInMBThruIST'
			
			-- using mysql.innodb_table_stats  
			, concat
			  (
			     round
			     (
			         (
			            ( 
			                tblITS.clustered_index_size 
			              + tblITS.sum_of_other_index_sizes 
			            )
			            * @@innodb_page_size
			            /
			            (
			              1024 * 1024 
			            )
			         )   
			       , 2
			     )
			     ,' MB'
			  ) 'tableSizeInMBThruINNODB'

			  
			, concat
			  (
			     round
			     (
			          (tblISSP.stat_value * @@innodb_page_size)
			         /(1024 * 1024 ) 
			       , 2
			     )
			     ,' MB'
			  ) 'indexSizeInMB'

			, date_format
			  (
			     tblIS.last_update
			     , '%Y-%m%-%d %H:%i %p'
			  ) as 'tsUpdate'
			  
			, concat
			  (
			  		truncate
			  		(
			      		( @@innodb_page_size / 1024 )
			      		, 0
			      	)
			      	, ' KB'
			  ) as '@@innodb_page_sizeInKB'

			  
	from   mysql.innodb_index_stats tblIS
 	
	left outer join mysql.innodb_index_stats tblISSP
	
		on   tblIS.database_name = tblISSP.database_name
		and  tblIS.table_name   = tblISSP.table_name
		and  tblIS.index_name   = tblISSP.index_name
		and  tblISSP.stat_name  = 'size'


/*		
	inner join information_schema.STATISTICS tblISS
	
		on   tblIS.database_name = tblISS.table_schema
		and  tblIS.table_name    = tblISS.table_name
		and  tblIS.index_name    = tblISS.index_name

*/
		
	inner join information_schema.TABLES tblIST
	
		on   tblIS.database_name = tblIST.table_schema
		and  tblIS.table_name    = tblIST.table_name
		
		
	inner join mysql.innodb_table_stats tblITS
	
		on   tblIS.database_name = tblITS.database_name
		and  tblIS.table_name    = tblITS.table_name
		
    where  tblIS.database_name = `database`

    and    (
    
                 ( tblIS.table_name = `table` )
              or (
                       ( `table` is null )
                    or ( `table` = '')
                 )
              
           )
           
           
    and    (
    
                 ( tblIS.index_name = `index` )
              or (
                       ( `index` is null )
                    or ( `index` = '')
                 )
              
           )
 
          
 
    /* Get Only the Top Index Column */       
    -- and  tblISS.SEQ_IN_INDEX = 1      


    and  tblIS.stat_name like 'n_diff_pfx%'
   
    /* Filter out Page Information */       
	/*
	    and  tblIS.stat_description not in
			 (
			      'Number of leaf pages in the index'
			    , 'Number of pages in the index'
			 )
	
	*/
    
    order by       
    
             tblIS.table_name
             
           , case
                 when tblIS.index_name = 'PRIMARY' then ''
                 else tblIS.index_name
             end
             
           , tblIS.stat_name 

    ;


end

//

 

Output

sakila.inventory

Image

Explanation

  1. Index :- Primary
    • Column Names :- inventory_id
    • Stat Value :- 4581
    • Sample Size :- 10 pages
    • Number of Pages :- 11
    • Size :- 0.17 MB
  2. Index :- idx_fk_film_id
    • Column Names :- film_id,inventory_id
    • Statistics
      • n_diff_pfx01
        • film_id
          • Stat :- 958
      • n_diff_pfx02
        • film_id,inventory_id
          • Stat :- 4581
    • Sample Size :- 4 pages
    • Number of Pages :- 5
    • Size :- 0.08 MB
  3. Index :- idx_store_id_film_id
    • Column Names :- store_id,film_id,inventory_id
    • Statistics
      • n_diff_pfx01
        • store_id
          • Stat :- 2
      • n_diff_pfx02
        • store_id,film_id
          • Stat :- 1521
    • Sample Size :- 4 pages
    • Number of Pages :- 7
    • Size :- 0.11 MB

 

Source Code Control

GitHub

DanielAdeniji/MySQL.IndexStatsInnoDB

Link

 

References

  1. MySQL
    • Reference
      • Statistics
        • Configuring Persistent Optimizer Statistics Parameters
          Link
      • Analyze
        • Innodb Analyze Table Complexity
          Link
      • INFORMATION_SCHEMA
        • INFORMATION_SCHEMA STATISTICS Table
          • Statistics Table
            Link
        • INFORMATION_SCHEMA COLUMNS Table
          • Columns Table
            Link
  2. Maria DB
    • System Tables
      • mysql.system_tables
        • mysql.innodb_index_stats
          • Home » Resources » Knowledge Base » Library » MariaDB Documentation » SQL Statements & Structure » SQL Statements » Administrative SQL Statements » System Tables » The mysql Database Tables » mysql.innodb_index_stats
            Link
  3. Index
    • Index Size
      • About MySQL
        • How to calculate a specific InnoDB Index Size
          Link

Leave a Reply

Please log in using one of these methods to post your comment:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google+ photo

You are commenting using your Google+ account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s