Re: Number of Rows in DB.

Michael Stassen Thu, 14 Oct 2004 07:22:56 -0700

Sujay,

The output of

  DESCRIBE table_name;

does not include number of rows.  Perhaps you meant

  SHOW TABLE STATUS LIKE 'table_name';

or simply

  SHOW TABLE STATUS;

as Michael suggested, to get output for each and every table. If so, it is important to note that the row count for InnoDB tables is an approximation, rather than an accurate count, so it won't help here. <http://dev.mysql.com/doc/mysql/en/SHOW_TABLE_STATUS.html>

Michael,

I'm not sure how helpful the total will be. Apparently, you are worried about the possibility of the standby db having the wrong number of rows, but in that scenario, isn't it possible that table A has lost a row, while table B has gained a row, yielding identical totals?

If you do a SHOW TABLE STATUS in the prod db and loop through the results, you can get the row counts for the MyISAM tables directly from the results and then do a SELECT COUNT(*) from the InnoDB tables to get their row counts. Now that you have numbers for each table, why not do the same thing in standby and compare the results table by table, rather than adding and comparing the total?

WARNING: Note that any inserts or deletes on prod between the mysqldump and the counting will throw off the results.

If you just want to verify that standby matches the dump, I suppose you could turn off extended inserts and count the number of inserts in the dumpfile with something like

  grep 'INSERT' dumpfile | wc -l

then compare that total to the total obtained by looping through the tables in standby, as described above. But I'm still skeptical of the usefulness of that test.

Michael

Sujay Koduri wrote:

hi all..

I think there is no way to count all the rows in a DB, using a single query.

we can say..

desc table_name; (gives all the tables in the DB)

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
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 bottom. In this case I needed to tweak it a bit by
adding some quotes (-Q) and extended inserts etc. Otherwise I was
gettting errors. But I ran the stock example on the man page for a couple
of years now on 2 other databases.

Mike

Sujay Koduri wrote:

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 of some use to me.

Thanks
sujay

-----Original Message-----
From: Michael J. Pawlowsky [mailto:[EMAIL PROTECTED]
Sent: Thursday, October 14, 2004 4:24 PM
To: [EMAIL PROTECTED]
Subject: Number of Rows in DB.

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 mysql dump nightly from a prod server piped
to a standy  server.
It's a 2 line shell script including outputting the date at time.

After the dump, I would like to output the number of rows in the prod
database and the newly updated standby database to make sure these
match.  The output is e-mailed to me, so in the morning I could simply
see if they match and be assured that the dump went well.

Thanks,
Mike

--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:    http://lists.mysql.com/[EMAIL PROTECTED]

Re: Number of Rows in DB.

Reply via email to