hi..
you can use...
select count(1) from table_name; (it will traverse only column 1)
as count(*) will go through all the columns, it will take much time compared
to this which generally will take less time.
by the way, can you send the shell script which you are using, it might be
I'm want ALL the rows from the WHOLE DATABASE not just one table.
I could do a show tables and then loop through each one and perform the
select count and add it up.
But I'm hoping to find a better way.
As for the script, simply read the mysqldump man page. It is in the
examples near the
]
Sent: Thursday, October 14, 2004 5:00 PM
To: [EMAIL PROTECTED]
Subject: Re: Number of Rows in DB.
I'm want ALL the rows from the WHOLE DATABASE not just one table.
I could do a show tables and then loop through each one and perform the
select count and add it up.
But I'm hoping to find
try: SHOW TABLE STATUS
Shawn Green
Database Administrator
Unimin Corporation - Spruce Pine
Michael J. Pawlowsky [EMAIL PROTECTED] wrote on 10/14/2004 06:54:16
AM:
Does anyone know of a quick way to get the number of rows in a database
from the command prompt.
I run a cron job that does a
)
and then get no of rows for each table..
Thanks
sujay
-Original Message-
From: Michael J. Pawlowsky [mailto:[EMAIL PROTECTED]
Sent: Thursday, October 14, 2004 5:00 PM
To: [EMAIL PROTECTED]
Subject: Re: Number of Rows in DB.
I want ALL the rows from the WHOLE DATABASE not just one table. I could
Note that SHOW TABLE STATUS counts for innodb are educated guesses --
innodb has to do a table scan to get the actual count.
On Thu, 14 Oct 2004 10:19:19 -0400, Michael Stassen
[EMAIL PROTECTED] wrote:
Sujay,
The output of
DESCRIBE table_name;
does not include number of rows.
Getting closer Thanks...
This db had LOTS of tables... That's why I'm simply trying to get the
total.
Is there a way to only get one column of this. then I can do something
like select SUM(show table status (rows));
[EMAIL PROTECTED] wrote:
try: SHOW TABLE STATUS
Shawn Green
Database
Well basically what I did was simply move a script that I've been using
on other databases and tried it on this new LARGE db.
And luckily the number or rows were off by something like 30,000 so it
was easy to figure out something went wrong (got total from phpMyAdmin)
I then tweak my dumps so
Michael J. Pawlowsky [EMAIL PROTECTED] wrote on 14/10/2004
17:01:34:
Getting closer Thanks...
This db had LOTS of tables... That's why I'm simply trying to get the
total.
Is there a way to only get one column of this. then I can do something
like select SUM(show table status
They do not... But I think I will just re-write my script in php
command line and loop through it.
It was just that I loved the simplicity of the bash script.
[EMAIL PROTECTED] wrote:
Michael J. Pawlowsky [EMAIL PROTECTED] wrote on 14/10/2004
17:01:34:
Getting closer Thanks...
This
Well, here's a quick and dirty way to do it:
#!/bin/sh
mkdir /tmp/count
chmod a+rwx /tmp/count
cd /tmp/count
mysqldump -T . production
wc -l *.txt production
rm *.sql *.txt
mysqldump -T . standby
wc -l *.txt standby
diff production standby
rm -rf /tmp/count
Thanks Eamon... I will think about it... But I'm looking at almost
1,000,000 rows.
Not sure this is the most effecient way. But I do appreciate the effort!
I think the idea of a php script that loops through show tables to get
the table names and then does a Select COUNT(*) on each one and
Would UNION help? Something like this:
select count(ID) as Total from categories where ID 5 UNION
select count(ID) as Total from products where ID 5 UNION
select count(ID) as Total from systems where ID 5
Then you could just sum Total?
-Original Message-
They do not... But I
: Thursday, October 14, 2004 12:19 PM
Subject: Re: Number of Rows in DB.
Thanks Eamon... I will think about it... But I'm looking at almost
1,000,000 rows.
Not sure this is the most effecient way. But I do appreciate the effort!
I think the idea of a php script that loops through show tables
SQL:
create TEMPORARY table A
select count(1) as C from TABLE1;
insert into A select count(1) as C from TABLE2;
.
select sum(C) from a;
drop table A;
Or
create TEMPORARY table A (C integer);
insert into A
select count(1) from TABLE1
union all
select count(1) from TABLE2
...
union all
15 matches
Mail list logo
