MySQL Forums
Forum List  »  Newbie

Re: Estimating Index Size on Innodb Tables
Posted by: Paulo Victor Maluf Alves
Date: July 27, 2010 02:43PM

Rick,

Thanks for your help!

I created the follow stored procedure for estimating index(works fine for me!):


DELIMITER $$

DROP PROCEDURE IF EXISTS `p_estimating_index` $$

CREATE PROCEDURE `p_estimating_index` (IN schema_name VARCHAR(20),
                                       IN table_name  VARCHAR(20),
                                       IN field_name  VARCHAR(20))
/* 
   Procedure: p_estimating_index 
   author   : Paulo Victor 
   email    : paulo.odin@gmail.com
*/

BEGIN	
	IF (schema_name = '' ) OR (table_name = '') OR (field_name = '') THEN
		SELECT   'Please enter all the parameters.' AS MSG_ERROR;
		
      ELSE	    
	  
	    SET @v_avg_column   = '';
		SET @v_count_table  = '';
		SET @v_column_type  = '';
		
		SET @sql1=CONCAT('SELECT AVG(LENGTH(',field_name,')) into @v_avg_column FROM ', schema_name,'.',table_name);		
		SET @sql2=CONCAT('SELECT COUNT(1) into @v_count_table FROM ', schema_name,'.',table_name);		
		
		PREPARE s1 FROM @sql1;
		PREPARE s2 FROM @sql2;
        
		EXECUTE s1;
		EXECUTE s2;
		
		DEALLOCATE PREPARE s1;		
		DEALLOCATE PREPARE s2;		
		
		SELECT data_type INTO @v_column_type FROM information_schema.columns WHERE TABLE_NAME = table_name AND column_name = field_name limit 1;
		
		-- STRING TYPE
		
		IF (@v_column_type = 'varchar') THEN		 
			SELECT FORMAT(((@v_avg_column+1+5)*2.8)*@v_count_table/1024/1024,2) ESTIMATED_MB;		 			
		
		  ELSEIF (@v_column_type = 'char') THEN
			SELECT FORMAT(((@v_avg_column+5)*2.8)*@v_count_table/1024/1024,2) ESTIMATED_MB;		 			
		
		  ELSEIF (@v_column_type = 'tinyblob') or (@v_column_type = 'tinytext') THEN
			SELECT FORMAT((@v_avg_column+5)*2.8*@v_count_table/1024/1024,2) ESTIMATED_MB;		 			

		  ELSEIF (@v_column_type = 'blob') or (@v_column_type = 'text') THEN
			SELECT FORMAT((@v_avg_column+2+5)*2.8*@v_count_table/1024/1024,2) ESTIMATED_MB;		 			

		  ELSEIF (@v_column_type = 'mediumblob') or (@v_column_type = 'mediumtext') THEN
			SELECT FORMAT((@v_avg_column+3+5)*2.8*@v_count_table/1024/1024,2) ESTIMATED_MB;		 			

		  ELSEIF (@v_column_type = 'longblob') or (@v_column_type = 'longtext') THEN
			SELECT FORMAT((@v_avg_column+4+5)*2.8*@v_count_table/1024/1024,2) ESTIMATED_MB;		 			
		  
          -- NUMBER TYPE
		  ELSEIF (@v_column_type = 'tinyint') THEN
			SELECT FORMAT((@v_avg_column+5)*2.8*@v_count_table/1024/1024,2) ESTIMATED_MB;		 			
			
		  ELSEIF (@v_column_type = 'smallint') THEN
			SELECT FORMAT((@v_avg_column+2+5)*2.8*@v_count_table/1024/1024,2) ESTIMATED_MB;		 			
			
		  ELSEIF (@v_column_type = 'mediumint') THEN
			SELECT FORMAT((@v_avg_column+3+5)*2.8*@v_count_table/1024/1024,2) ESTIMATED_MB;		 			
			
		  ELSEIF (@v_column_type = 'int') or (@v_column_type = 'integer') THEN
			SELECT FORMAT((@v_avg_column+4+5)*2.8*@v_count_table/1024/1024,2) ESTIMATED_MB;		 			
		  
		  ELSEIF (@v_column_type = 'bigint') THEN
			SELECT FORMAT((@v_avg_column+8+5)*2.8*@v_count_table/1024/1024,2) ESTIMATED_MB;		 			
			
		  -- ELSE IF (@v_column_type = 'float') THEN
		  -- 4 bytes if 0 <= p <= 24, 8 bytes if 25 <= p <= 53
		 
		  ELSEIF (@v_column_type = 'double') THEN
			SELECT FORMAT((@v_avg_column+8+5)*2.8*@v_count_table/1024/1024,2) ESTIMATED_MB;		 			
			
		  --  ELSE IF (@v_column_type = 'bit') THEN
		  -- approximately (M+7)/8 bytes
			
		  --  ELSE IF (@v_column_type = 'decimal') or (@v_column_type = 'numeric') THEN
		  -- Varies; see following discussion
		  
          -- DATE AND TIME TYPE		  
		  ELSEIF (@v_column_type = 'date') or (@v_column_type = 'time') THEN
		    SELECT FORMAT((@v_avg_column+3+5)*@v_count_table/1024/1024,2) ESTIMATED_MB;		 			
			
		  ELSEIF (@v_column_type = 'datetime') THEN
		    SELECT FORMAT((@v_avg_column+8+5)*@v_count_table/1024/1024,2) ESTIMATED_MB;		 			
			
		  ELSEIF (@v_column_type = 'time') THEN
			SELECT FORMAT((@v_avg_column+3+5)*@v_count_table/1024/1024,2) ESTIMATED_MB;		 			
			
		  ELSEIF (@v_column_type = 'year') THEN
			SELECT FORMAT((@v_avg_column+5)*@v_count_table/1024/1024,2) ESTIMATED_MB;	
		  
		  ELSE 
		   SELECT 'DATA TYPE NOT SUPPORTED' AS MSG_ERROR; 
		  
		 END IF;
		
    END IF;	

END $$

DELIMITER ;


Options: ReplyQuote


Subject
Written By
Posted
Re: Estimating Index Size on Innodb Tables
July 27, 2010 02:43PM


Sorry, you can't reply to this topic. It has been closed.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.