Re: MySQL ResultSet - count rows?

Lew <lew@xxxxxxxxxxxxx> writes:

Dyreatnews@xxxxxxx wrote:
No. You could execute 'SELECT COUNT(*) ...' first but then you
essentially traverse all the rows twice.

Ruud de Koter wrote:
One should realize that in doing so you are not determining the
number of rows at the moment of retrieval, but the number of rows a
few moments before the actual retrieval. Whether this is an
important distinction depends with the volatility of the table and
the use you will make if this number. On any system used by more
than one user/process, these numbers *will* be different, sooner or
later.

In short: this is not the way to go.

You could wrap the SELECT COUNT(*) and the row SELECT in a
transaction, thus guaranteeing at least a consistent view if not a
current one.

Ruud is making a very good point. I've just been told that the the
SELECT COUNT(*) approach will only be guaranteed to work if you execute
both SELECTs in a single transaction AND use SERIALIZABLE isolation
level. Even at REPEATABLE READ you may see new rows added since the
previous select, (but all rows counted will have the same value
throughout the transaction).

--
dt

Questions about Derby/Java DB? Please visit
http://db.apache.org/derby/derby_mail.html
.