Unexpected Select Output

Dear all,

Today I shoot a query to know the size of tables in a particular
database, but don't know why it prints only the output of only one
table. Here is my query & output :

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_web' and table_schema='pdc_crawler';
-> ;
+-------------+---------------+--------+--------+--------+
| database | Table | sizeGB | sizeMB | sizeKB |
+-------------+---------------+--------+--------+--------+
| pdc_crawler | hc_categories | 0.00G | 0.06M | 58.71K |
+-------------+---------------+--------+--------+--------+


Why it is not able to print all table that fits in OR condition.


Thanks & best Regards,

Adarsh Sharma

--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/mysql?unsub=gcdmg-mysql-2@m.gmane.org

Re: Unexpected Select Output

--=_b424f8e4-e1e1-45c8-921a-7fbc52ac26f8
Content-Type: text/plain; charset=utf-8
Content-Transfer-Encoding: 7bit

Probably not the cause, but you should know that and binds more tightly than or, so what you've written is actually

WHERE (table_name = 'hc_categories')
OR (table_name = 'hc_master')
OR (table_name = 'hc_web' AND table_schema = 'pdc_crawler')
What you probably mean is

WHERE (table_name = 'hc_categories' OR table_name = 'hc_master' OR table_name = 'hc_web')
AND (table_schema = 'pdc_crawler')

As to why the query doesn't provide the three tables... not really an idea - the contents of your information_schema.tables table might provide insight :-)

----- Original Message -----

> From: "Adarsh Sharma"
> To: mysql@lists.mysql.com
> Sent: Tuesday, 15 March, 2011 5:56:17 AM
> Subject: Unexpected Select Output

> Dear all,

> Today I shoot a query to know the size of tables in a particular
> database, but don't know why it prints only the output of only one
> table. Here is my query & output :

> 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_web' and table_schema='pdc_crawler';
> -> ;
> +-------------+---------------+--------+--------+--------+

> +-------------+---------------+--------+--------+--------+

> +-------------+---------------+--------+--------+--------+

> Why it is not able to print all table that fits in OR condition.

> Thanks & best Regards,

> Adarsh Sharma

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

--=_b424f8e4-e1e1-45c8-921a-7fbc52ac26f8--

Re: Unexpected Select Output

--=_3e9f6a42-0760-46ae-9be2-f4ca47992437
Content-Type: text/plain; charset=utf-8
Content-Transfer-Encoding: 7bit

----- Original Message -----

> From: "Adarsh Sharma"

> 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

--=_3e9f6a42-0760-46ae-9be2-f4ca47992437--