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 ;