Re^2: prepare statement within DBI

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

Comment on Re^2: prepare statement within DBI

Replies are listed 'Best First'.
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]