CODEWITHSUNDEEP
mysql
curinno
curinno

Reputation: 38

how to get size of each indices in MySQL

I'm trying to optimize sizes of indices which is necessary for daily statistical job, so that I can reduce table size and making the daily statistical job slower as a tradeoff.

In order to do that, I'd like to get size of each index in a table.

"show table status from" only shows Index_length which is total size of all indices in a table.

Is there a way to retrieve sizes of every indicies in a table?

Upvotes: 1

Views: 235

Answers (2)

Lebnik
Lebnik

Reputation: 636

example on php:

<?php
error_reporting(E_ERROR | E_PARSE | E_COMPILE_ERROR);

$c = mysql_connect('localhost', 'user_root', 'user_root_pass');

function filesize_($size=0, $round=2){
        $type = array('bytes', 'Kb',  'Mb', 'Gb', 'Tb', 'Pb');
        for ($i=0; $size>1024; $size=$size/1024){$i++;}
        if(empty($i)){$i='0';}
        return round($size, $round).' '.$type[$i];
}

if( !$c ){
    echo 'mysql not found: '.__FILE__; exit;
}
$sum = array();
$sql = 'SHOW DATABASES';
if( $q = mysql_query('SHOW DATABASES') ){
        $a = array();
        while($r = mysql_fetch_row($q) ){
                $a[] = $r['0'];
        }
        foreach($a as $name){
                $sql = 'error select db: '.$name;
                if( @mysql_select_db($name, $c)  ){
                        $sql = 'error SHOW TABLE STATUS: '.$name;
                        if($q = mysql_query('SHOW TABLE STATUS')){
                                while($r = mysql_fetch_assoc($q) ){
                                        $sum[ $r['Engine'] ] += $r['Index_length'];
                                }
                        }else{
                                echo $sql."\n";
                        }
                }else{
                        echo $sql."\n";
                }
        }
}else{
        echo 'error:'.$sql;
}
echo 'Engine indexes sum:'."\n";
foreach($sum as $engine => $count){
        echo $engine."\t".filesize_($count)."\n";
}
echo 'thats all.'."\n";

to view the result script run, example: php this_script.php and you get the result:

Engine indexes sum:
MEMORY  0 bytes
MyISAM  6.84 Gb
InnoDB  416 Kb
CSV 0 bytes
thats all.

Upvotes: 1

DonCallisto
DonCallisto

Reputation: 29912

For indexes selection, you can follow this

So, SHOW TABLE STATUS [{FROM | IN} db_name] [LIKE 'pattern' | WHERE expr]

Upvotes: 0

Related Questions