Re: count(*) on different tables
is it possible to get *one* quick result with the rowcount of each table in one database, without knowing the column-names? this does *not* work: select count(t1.*), count(t2.*) from table1 t1, table2 t2 Select 'Table1' as tableName, count(*) as rowCount from table1 UNION Select 'Table2' as tableName, count(*) as rowCount from table2 ... etc Assuming that MySQL can use the UNION clause. (I haven't checked) I think MySQL actually doesn't support union. ;/ I wonder if this is so difficult.. Only getting rowcounts of different tables in one result. I tried also the folliowing: select count(*) from table1, table2. But this (curiously) returns table1rowcount * table2rowcount Greetings, Ansgar - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: count(*) on different tables
UNION is currently not supported, though one workaround is merge tables, it's not quite the same thing. As to your query SELECT COUNT(*) FROM table1,table2, it is returning the expected result. Without a WHERE clause, you are getting the cartesian product of both tables (all rows from table1 joined to all rows of table2). Ansgar Becker [EMAIL PROTECTED] wrote: is it possible to get *one* quick result with the rowcount of each table in one database, without knowing the column-names? this does *not* work: select count(t1.*), count(t2.*) from table1 t1, table2 t2 Select 'Table1' as tableName, count(*) as rowCount from table1 UNION Select 'Table2' as tableName, count(*) as rowCount from table2 ... etc Assuming that MySQL can use the UNION clause. (I haven't checked) I think MySQL actually doesn't support union. ;/ I wonder if this is so difficult.. Only getting rowcounts of different tables in one result. I tried also the folliowing: select count(*) from table1, table2. But this (curiously) returns table1rowcount * table2rowcount Greetings, Ansgar - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
RE: count(*) on different tables
I had the same problem to solv before, and this is what I did. PS: I don't know if this is the right way to do it, but it worked for me. mysql CREATE TABLE Test.myTempTable (myTable VARCHAR(20), nrows AS INTEGER); mysql INSERT INTO Test.myTempTable SELECT Table1, COUNT(*) FROM Table1; // 10 rows mysql INSERT INTO Test.myTempTable SELECT Table2, COUNT(*) FROM Table2; // 265 rows mysql SELECT * FROM Test.myTempTable; ++--+ | myTable| nrows| ++--+ | Table1 | 10 | | Table2 | 265 | ++--+ 2 rows in set (0.00 sec) mysql DROP TABLE Test.myTempTable; // Finally drop the temp table It is a little hard to code it in a application since you need to RUN 4 statements and treat each result in a separated row. But you can use this to count any quantity of tables. []'s Crercio O. Silva - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
count(*) on different tables
Hi, is it possible to get *one* quick result with the rowcount of each table in one database, without knowing the column-names? this does *not* work: select count(t1.*), count(t2.*) from table1 t1, table2 t2 Manual doesn't say anything about that. Greetings, Ansgar Becker - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: count(*) on different tables
If you are using PHP (or anothe controlling language, you could take the results of SHOW TABLES and loop through it either doing a single select for each table or building a select statement that UNIONs the results together. i.e. Select 'Table1' as tableName, count(*) as rowCount from table1 UNION Select 'Table2' as tableName, count(*) as rowCount from table2 ... etc Assuming that MySQL can use the UNION clause. (I haven't checked) My DBA says that in MS T/SQL you can do this: select solcount = count( distinct s.solKey ), probcount = count( distinct p.probKey ) from sol s, prob p where SOL and PROB are tables. HTH, Cal http://www.calevans.com - Original Message - From: Ansgar Becker [EMAIL PROTECTED] To: [EMAIL PROTECTED] Sent: Monday, May 21, 2001 1:18 PM Subject: count(*) on different tables Hi, is it possible to get *one* quick result with the rowcount of each table in one database, without knowing the column-names? this does *not* work: select count(t1.*), count(t2.*) from table1 t1, table2 t2 Manual doesn't say anything about that. Greetings, Ansgar Becker - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: count(*) on different tables
Well ... I don't know enough about mysql but it must allow you somehow to specify the column by number as in Oracle and other DBs SQL select count(*) from product; COUNT(*) -- 10 SQL select count(1) from product; COUNT(1) -- 10 Once you just want to count the rows the performance of your select will be much better if you specify a column rather than *. Siomara From: Ansgar Becker [EMAIL PROTECTED] To: [EMAIL PROTECTED] Subject: count(*) on different tables Date: Mon, 21 May 2001 20:18:35 +0200 Hi, is it possible to get *one* quick result with the rowcount of each table in one database, without knowing the column-names? this does *not* work: select count(t1.*), count(t2.*) from table1 t1, table2 t2 Manual doesn't say anything about that. Greetings, Ansgar Becker - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php _ Get Your Private, Free E-mail from MSN Hotmail at http://www.hotmail.com. - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: count(*) on different tables
Actually, as far as performance goes, it depends on table types. Most of the MySQL table handlers keep an internal count of record numbers. Thus, a select count(*) from table; is as fast as it gets. - Original Message - From: Siomara Pantarotto [EMAIL PROTECTED] To: [EMAIL PROTECTED] Sent: Monday, May 21, 2001 12:26 PM Subject: Re: count(*) on different tables Well ... I don't know enough about mysql but it must allow you somehow to specify the column by number as in Oracle and other DBs SQL select count(*) from product; COUNT(*) -- 10 SQL select count(1) from product; COUNT(1) -- 10 Once you just want to count the rows the performance of your select will be much better if you specify a column rather than *. Siomara From: Ansgar Becker [EMAIL PROTECTED] To: [EMAIL PROTECTED] Subject: count(*) on different tables Date: Mon, 21 May 2001 20:18:35 +0200 Hi, is it possible to get *one* quick result with the rowcount of each table in one database, without knowing the column-names? this does *not* work: select count(t1.*), count(t2.*) from table1 t1, table2 t2 Manual doesn't say anything about that. Greetings, Ansgar Becker - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php _ Get Your Private, Free E-mail from MSN Hotmail at http://www.hotmail.com. - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: count(*) on different tables
Cool but how about if you put a where clause to your select?? SQL select count(1) from product where productid 3; COUNT(1) -- 7 Would count(*) show the same performance as count(1)??? Siomara From: Eric Fitzgerald [EMAIL PROTECTED] To: Siomara Pantarotto [EMAIL PROTECTED], [EMAIL PROTECTED] Subject: Re: count(*) on different tables Date: Mon, 21 May 2001 13:11:38 -0700 Actually, as far as performance goes, it depends on table types. Most of the MySQL table handlers keep an internal count of record numbers. Thus, a select count(*) from table; is as fast as it gets. - Original Message - From: Siomara Pantarotto [EMAIL PROTECTED] To: [EMAIL PROTECTED] Sent: Monday, May 21, 2001 12:26 PM Subject: Re: count(*) on different tables Well ... I don't know enough about mysql but it must allow you somehow to specify the column by number as in Oracle and other DBs SQL select count(*) from product; COUNT(*) -- 10 SQL select count(1) from product; COUNT(1) -- 10 Once you just want to count the rows the performance of your select will be much better if you specify a column rather than *. Siomara From: Ansgar Becker [EMAIL PROTECTED] To: [EMAIL PROTECTED] Subject: count(*) on different tables Date: Mon, 21 May 2001 20:18:35 +0200 Hi, is it possible to get *one* quick result with the rowcount of each table in one database, without knowing the column-names? this does *not* work: select count(t1.*), count(t2.*) from table1 t1, table2 t2 Manual doesn't say anything about that. Greetings, Ansgar Becker - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php _ Get Your Private, Free E-mail from MSN Hotmail at http://www.hotmail.com. - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php _ Get Your Private, Free E-mail from MSN Hotmail at http://www.hotmail.com. - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: count(*) on different tables
Well, let's do a test: mysql CREATE TABLE testcount (product INT NOT NULL AUTO_INCREMENT PRIMARY KEY, description VARCHAR(50)); Query OK, 0 rows affected (0.00 sec) **Ran perl script here to populate the table** mysql SELECT * FROM testcount LIMIT 10; +-+---+ | product | description | +-+---+ | 1 | Decription 1 | | 2 | Decription 2 | | 3 | Decription 3 | | 4 | Decription 4 | | 5 | Decription 5 | | 6 | Decription 6 | | 7 | Decription 7 | | 8 | Decription 8 | | 9 | Decription 9 | | 10 | Decription 10 | +-+---+ 10 rows in set (0.03 sec) mysql SELECT COUNT(*) FROM testcount; +--+ | COUNT(*) | +--+ | 10 | +--+ 1 row in set (0.00 sec) mysql SELECT COUNT(1) FROM testcount; +--+ | COUNT(1) | +--+ | 10 | +--+ 1 row in set (0.00 sec) mysql SELECT COUNT(2) FROM testcount; +--+ | COUNT(2) | +--+ | 10 | +--+ 1 row in set (0.00 sec) mysql SELECT COUNT(*) FROM testcount WHERE product 10; +--+ | COUNT(*) | +--+ |0 | +--+ 1 row in set (0.32 sec) mysql SELECT COUNT(1) FROM testcount WHERE product 10; +--+ | COUNT(1) | +--+ |0 | +--+ 1 row in set (0.32 sec) mysql As you can see, it really makes no difference to MySQL. - Original Message - From: Siomara Pantarotto [EMAIL PROTECTED] To: [EMAIL PROTECTED]; [EMAIL PROTECTED] Sent: Monday, May 21, 2001 1:48 PM Subject: Re: count(*) on different tables Cool but how about if you put a where clause to your select?? SQL select count(1) from product where productid 3; COUNT(1) -- 7 Would count(*) show the same performance as count(1)??? Siomara From: Eric Fitzgerald [EMAIL PROTECTED] To: Siomara Pantarotto [EMAIL PROTECTED], [EMAIL PROTECTED] Subject: Re: count(*) on different tables Date: Mon, 21 May 2001 13:11:38 -0700 Actually, as far as performance goes, it depends on table types. Most of the MySQL table handlers keep an internal count of record numbers. Thus, a select count(*) from table; is as fast as it gets. - Original Message - From: Siomara Pantarotto [EMAIL PROTECTED] To: [EMAIL PROTECTED] Sent: Monday, May 21, 2001 12:26 PM Subject: Re: count(*) on different tables Well ... I don't know enough about mysql but it must allow you somehow to specify the column by number as in Oracle and other DBs SQL select count(*) from product; COUNT(*) -- 10 SQL select count(1) from product; COUNT(1) -- 10 Once you just want to count the rows the performance of your select will be much better if you specify a column rather than *. Siomara From: Ansgar Becker [EMAIL PROTECTED] To: [EMAIL PROTECTED] Subject: count(*) on different tables Date: Mon, 21 May 2001 20:18:35 +0200 Hi, is it possible to get *one* quick result with the rowcount of each table in one database, without knowing the column-names? this does *not* work: select count(t1.*), count(t2.*) from table1 t1, table2 t2 Manual doesn't say anything about that. Greetings, Ansgar Becker - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php _ Get Your Private, Free E-mail from MSN Hotmail at http://www.hotmail.com. - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php _ Get Your Private, Free E-mail from MSN Hotmail at http://www.hotmail.com. - Before posting, please check: http
Re: count(*) on different tables
That's really cool Thanks siomara From: Eric Fitzgerald [EMAIL PROTECTED] To: Siomara Pantarotto [EMAIL PROTECTED], [EMAIL PROTECTED] Subject: Re: count(*) on different tables Date: Mon, 21 May 2001 14:08:00 -0700 Well, let's do a test: mysql CREATE TABLE testcount (product INT NOT NULL AUTO_INCREMENT PRIMARY KEY, description VARCHAR(50)); Query OK, 0 rows affected (0.00 sec) **Ran perl script here to populate the table** mysql SELECT * FROM testcount LIMIT 10; +-+---+ | product | description | +-+---+ | 1 | Decription 1 | | 2 | Decription 2 | | 3 | Decription 3 | | 4 | Decription 4 | | 5 | Decription 5 | | 6 | Decription 6 | | 7 | Decription 7 | | 8 | Decription 8 | | 9 | Decription 9 | | 10 | Decription 10 | +-+---+ 10 rows in set (0.03 sec) mysql SELECT COUNT(*) FROM testcount; +--+ | COUNT(*) | +--+ | 10 | +--+ 1 row in set (0.00 sec) mysql SELECT COUNT(1) FROM testcount; +--+ | COUNT(1) | +--+ | 10 | +--+ 1 row in set (0.00 sec) mysql SELECT COUNT(2) FROM testcount; +--+ | COUNT(2) | +--+ | 10 | +--+ 1 row in set (0.00 sec) mysql SELECT COUNT(*) FROM testcount WHERE product 10; +--+ | COUNT(*) | +--+ |0 | +--+ 1 row in set (0.32 sec) mysql SELECT COUNT(1) FROM testcount WHERE product 10; +--+ | COUNT(1) | +--+ |0 | +--+ 1 row in set (0.32 sec) mysql As you can see, it really makes no difference to MySQL. - Original Message - From: Siomara Pantarotto [EMAIL PROTECTED] To: [EMAIL PROTECTED]; [EMAIL PROTECTED] Sent: Monday, May 21, 2001 1:48 PM Subject: Re: count(*) on different tables Cool but how about if you put a where clause to your select?? SQL select count(1) from product where productid 3; COUNT(1) -- 7 Would count(*) show the same performance as count(1)??? Siomara From: Eric Fitzgerald [EMAIL PROTECTED] To: Siomara Pantarotto [EMAIL PROTECTED], [EMAIL PROTECTED] Subject: Re: count(*) on different tables Date: Mon, 21 May 2001 13:11:38 -0700 Actually, as far as performance goes, it depends on table types. Most of the MySQL table handlers keep an internal count of record numbers. Thus, a select count(*) from table; is as fast as it gets. - Original Message - From: Siomara Pantarotto [EMAIL PROTECTED] To: [EMAIL PROTECTED] Sent: Monday, May 21, 2001 12:26 PM Subject: Re: count(*) on different tables Well ... I don't know enough about mysql but it must allow you somehow to specify the column by number as in Oracle and other DBs SQL select count(*) from product; COUNT(*) -- 10 SQL select count(1) from product; COUNT(1) -- 10 Once you just want to count the rows the performance of your select will be much better if you specify a column rather than *. Siomara From: Ansgar Becker [EMAIL PROTECTED] To: [EMAIL PROTECTED] Subject: count(*) on different tables Date: Mon, 21 May 2001 20:18:35 +0200 Hi, is it possible to get *one* quick result with the rowcount of each table in one database, without knowing the column-names? this does *not* work: select count(t1.*), count(t2.*) from table1 t1, table2 t2 Manual doesn't say anything about that. Greetings, Ansgar Becker - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php _ Get Your Private, Free E-mail from MSN Hotmail at http://www.hotmail.com. - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED
Re: count(*) on different tables
As far as I know, they should be the same. The only case in which I can see count(colname) would be slower is if it doesnt count rows that contain null values for that column (this is only a guess, I dont know if count does that or not). ryan Would count(*) show the same performance as count(1)??? Siomara Actually, as far as performance goes, it depends on table types. Most of the MySQL table handlers keep an internal count of record numbers. Thus, a select count(*) from table; is as fast as it gets. Well ... I don't know enough about mysql but it must allow you somehow to specify the column by number as in Oracle and other DBs SQL select count(*) from product; COUNT(*) -- 10 SQL select count(1) from product; COUNT(1) -- 10 Once you just want to count the rows the performance of your select will be much better if you specify a column rather than *. Siomara Hi, is it possible to get *one* quick result with the rowcount of each table in one database, without knowing the column-names? this does *not* work: select count(t1.*), count(t2.*) from table1 t1, table2 t2 Manual doesn't say anything about that. Greetings, Ansgar Becker - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
