Faster way of retrieving the number of recs in mysql

Andre_br has asked for the wisdom of the Perl Monks concerning the following question:

Hello folks, I am having trouble having DBI tell me the number of rows in a table. The regular way, I know, is to query 'select * from table' and store the number of rows returned by the $sth->execute( ); DBI method. The problem is that this isn´t working for me when I do a SELECT query without restrictions (as I was supposed to do in this case). Anyone knows any other way of getting the number of records? Is there any way to do that without a query? I mean, I have a table that will soon reach some hundred thousand records and 'select * from table' is a hell of a work to put in a script that´s supposed to output in my site´s front door the number of records available for searching. I wait for thy wisdom. Thanks a lot, my friends! André

Comment on Faster way of retrieving the number of recs in mysql

Replies are listed 'Best First'.
Re: Faster way of retrieving the number of recs in mysql by Zaxo (Archbishop) on Dec 13, 2004 at 05:03 UTC
`my $sql = 'select count(*) from thetable';` After Compline, Zaxo	[reply] [d/l]
Re: Faster way of retrieving the number of recs in mysql by shenme (Priest) on Dec 13, 2004 at 05:09 UTC
execute() starts a query, it doesn't necessarily _accomplish_ the query. (You may have been thinking of do() ) You must fetch the results. If you are only interested in the count of rows or rows that would match, then perhaps you should use "select count() from table", and then use selectrow_array() to fetch the single value returned. Explicitly asking for count() may allow some DBs to execute the request a lot faster than retrieving all that muck just to count.	[reply]
Re: Faster way of retrieving the number of recs in mysql by sasikumar (Monk) on Dec 13, 2004 at 06:25 UTC
Hi Better to use `select count(*) from table where <condition>` This is the fastest way possible. It will not overload your DB. Thanks Sasi Kumar	[reply] [d/l]
Re^2: Faster way of retrieving the number of recs in mysql by Thilosophy (Curate) on Dec 13, 2004 at 09:23 UTC
And unless necessary, omit the `where` clause. In MySQL, a `count(*)` can be answered just by querying the table meta data (VERY FAST), whereas conditions in the `where` clause will result in index or even table scans (ranging from NOT SO FAST to SLOW).	[reply]
Re^3: Faster way of retrieving the number of recs in mysql by Andre_br (Pilgrim) on Dec 14, 2004 at 00:15 UTC
In deed, that´s a great solution! It´s working just great now! Thanks a lot my friends!	[reply]