Re: prepare statement within DBI

There is only one way to find out: try both approaches and time x runs and see what is best.

COUNT(*) should be highly optimized on the database server side, probably not even needing to run through the whole of the datatable, so it could indeed be that most of the time is spent in connecting to the DB and preparing the query.

On a MySQL database with about 70,000 records, COUNT(*) took 1/10 of a second to come back with the result. The database was on the local machine, so transmission time was neglible.

Querying a table with only 27 records, took 0.10 seconds as well.

Probably 0.10 of second is the minimum time to run a query.

CountZero

"If you have four groups working on a compiler, you'll get a 4-pass compiler." - Conway's Law

Comment on Re: prepare statement within DBI Select or Download Code

Replies are listed 'Best First'.
Re^2: prepare statement within DBI by dragonchild (Archbishop) on Aug 25, 2004 at 20:00 UTC
Note - MyISAM and Heap tables will return the count in constant time. InnoDB and BDB tables will not. It's a function of the data structure used under the covers. Oracle tables are worse than MyISAM, but better than InnoDB, in that the cost of count scales logarithmically, not linearly. ------ We are the carpenters and bricklayers of the Information Age. Then there are Damian modules.... sigh* ... that's not about being less-lazy -- that's about being on some really good drugs -- you know, there is no spoon.* - flyingmoose I shouldn't have to say this, but any code, unless otherwise stated, is untested	[reply]
Re^3: prepare statement within DBI by mpeppler (Vicar) on Aug 26, 2004 at 07:07 UTC
A `select count()` usually* has to traverse at least an index tree to find the actual number of rows currently in the table. A quick test on Sybase on a fairly busy server shows that it can take up to 3 seconds to count the rows in a 4+ million row table, with a lot of the time spent fetching data pages into cache. Keep in mind that the database engine has to be able to run the `select count(*) where somecondition` as well, for which no real short-cut is available, unless an appropriate index exists that can satisfy the WHERE clause. Michael	[reply] [d/l] [select]
Re^4: prepare statement within DBI by dragonchild (Archbishop) on Aug 26, 2004 at 12:06 UTC
From what I understood in my MySQL course a few months back, MyISAM tables actually keep the number of rows in the table stored somewhere. I'm not sure why it does this, but it does. So, `select count() from table1;` is actually special-cased in the MySQL code. ------ We are the carpenters and bricklayers of the Information Age.* Then there are Damian modules.... sigh* ... that's not about being less-lazy -- that's about being on some really good drugs -- you know, there is no spoon.* - flyingmoose I shouldn't have to say this, but any code, unless otherwise stated, is untested	[reply] [d/l]