[Solved] How to get table size ?

[caromax]

Hi,

I want to get size of customer table (in Mo).

 

What SQL query should I do to get it?

 

Thank you to all

Edited May 27, 2013 by caromax (see edit history)

[vekia]

if you've got an ability - you can log in to phpmyadmin, select your database and on tables list you've got row "sizes" with information about table size:

 

 

 

you can also use this query:

SELECT TABLE_NAME, table_rows, data_length, index_length,
round(((data_length + index_length) / 1024 / 1024),2) "Size in MB"
FROM information_schema.TABLES WHERE table_schema = "1541a"; AND table_name='ps_customer'

 

don't forget to change:

table_schema = "1541a"; // your database

[caromax]

Hi,

First of all, thank you to help me

 

I try :

 $table_size = 0;
 $results = Db::getInstance()->Execute('
  SELECT " '._DB_PREFIX_.' "custumer, table_rows, data_length, index_length,
  round(((data_length + index_length) / 1024 / 1024),2) "Size in MB"
  FROM information_schema.tables
  WHERE table_schema = " '._DB_NAME_.' "
  AND table_name = " '._DB_PREFIX_.' "customer;
  '))
  $table_size = mysql_result($results,0);
 return $table_size;

 

But it's don't work.

[vekia]

it doesn't work because you use wrong method for query, moreover - you've got errors in the query (" signs around the constants)

 

here is proper method:

 

$query="SELECT TABLE_NAME, table_rows, data_length, index_length, round(((data_length + index_length) / 1024 / 1024),2) 'Size in MB' FROM information_schema.TABLES WHERE table_schema = '"._DB_NAME_."' AND table_name='"._DB_PREFIX_."customer'";

$results = Db::getInstance()->ExecuteS($query);

 

you have to use ExecuteS command.

 

effect:

Array ( [0] => Array ( [TABLE_NAME] => ps_customer [table_rows] => 2 [data_length] => 16384 [index_length] => 98304 [size in MB] => 0.11 ) )

 

array with one row - information about table

[vekia]

@caromax have you checked it? please let me know if it works for you or not

[caromax]

Hi vekia,

Thank you very mutch for your help.

I tryed the code:

$query="SELECT TABLE_NAME, table_rows, data_length, index_length, round(((data_length + index_length) / 1024 / 1024),2) 'Size in MB' FROM information_schema.TABLES WHERE table_schema = '"._DB_NAME_."' AND table_name='"._DB_PREFIX_."customer'";
$results = Db::getInstance()->ExecuteS($query);

 

and a try to display result like :

'.$results.'

so i have an Array

An idea ?

Edited May 27, 2013 by caromax (see edit history)

[vekia]

yes - everything seems to be fine, because $result is an array:

Array ( [0] => Array ( [TABLE_NAME] => ps_customer [table_rows] => 2 [data_length] => 16384 [index_length] => 98304 [size in MB] => 0.11 ) )

 

you have to use something like:

{$result[0]['Size in MB']}

[caromax]

Many thanks.

I understood.

This works well.

I do not know enough to say you thank you!

[vekia]

you're welcome

 

thanks for marking this thread as solved

 

if you've got any other questions related to this case - feel free to continue discussion here

 

regards

[caromax]

Yes, it is already marked as solved.

Just one question. I use the query for the BO. How to dont use 'Size in MB' in the query ?

Keep quote 'Size in MB' empty like '' ?

 

$query="SELECT TABLE_NAME, table_rows, data_length, index_length, round(((data_length + index_length) / 1024 / 1024),2) '' FROM information_schema.TABLES WHERE table_schema = '"._DB_NAME_."' AND table_name='"._DB_PREFIX_."customer'";
$results = Db::getInstance()->ExecuteS($query);

 

In BO page, I would like this :

'.$results[0].' '.$this->l('MB').'

It's possible ?

[vekia]

you can use empty field name but you will have got troubles with usage {$results[0][]} in the template file.

its better to use name, for example 'mb' then you must use {$results[0]['mb']}

[caromax]

Thank you very much,

Your answers have been of a great help