List:General Discussion« Previous MessageNext Message »
From:Johan De Meersman Date:March 15 2011 7:04am
Subject:Re: Unexpected Select Output
View as plain text  
----- Original Message -----

> From: "Adarsh Sharma" <adarsh.sharma@stripped>

> I am able to fetch the output individually, but I try that I access
> all information through one command :

> mysql> SELECT table_schema 'database',table_name 'Table', concat(
> round( sum( data_length + index_length ) / ( 1024*1024*1024) , 2 ) ,
> 'G' ) sizeGB,concat( round( sum( data_length + index_length ) / (
> 1024*1024) , 2 ) , 'M' ) sizeMB,concat( round( sum( data_length +
> index_length ) / ( 1024) , 2 ) , 'K' ) sizeKB FROM
> information_schema.TABLES WHERE (table_name ='hc_categories')
> -> OR (table_name='hc_master') OR (table_name='hc_source') OR
> (table_name='job_queue') OR (table_name='master_seed')
> -> OR (table_name='metadata') OR (table_name='page_content') OR
> (table_name='page_crawled') OR (table_name='url_statistics')
> -> OR (table_name='website_authentication') OR
> (table_name='website_internalurl') OR (table_name='website_master');

Hmmm. I hadn't noticed yet, but you're using sum() in there, and you're not using a group
by - I'm pretty sure this isn't your full query; and the addition of a group by clause
would explain why you only get one row. 

Sum() does vertical summing, not horizontal; that is, it sums all values of the same
column per GROUP BY resultset, not the values you give it for each row. You don't want
sum(data_length + index_length) , you just want (data_length + index_length) , I bet. 

Try this: 

SELECT table_schema 'database', table_name 'Table', 
concat(round((data_length + index_length) / (1024*1024*1024), 2), 'G' ) sizeGB, 
concat(round((data_length + index_length) / (1024*1024), 2), 'M') sizeMB, 
concat(round((data_length + index_length) / (1024) ,2), 'K') sizeKB 
FROM information_schema.TABLES 
WHERE table_schema = 'pdc_crawler' 
AND table_name IN ('hc_categories', 'hc_master', 'hc_source', 'job_queue', 'master_seed',
'metadata', 'page_content', 
'page_crawled', 'url_statistics', 'website_authentication', 'website_internalurl',
'website_master'); 

-- 
Bier met grenadyn 
Is als mosterd by den wyn 
Sy die't drinkt, is eene kwezel 
Hy die't drinkt, is ras een ezel 

Thread
Unexpected Select OutputAdarsh Sharma15 Mar
  • Re: Unexpected Select OutputJohan De Meersman15 Mar
Re: Unexpected Select OutputJohan De Meersman15 Mar