Re: MySQL ResultSet - count rows?

Lew wrote:
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.

True. I may have gotten carried away as I was writing. What matters is that the double-select solution has its issues. One should be aware of those and decide whether these are acceptable, can be mitigated (as you propose), or are prohibitive.

Regards,

Ruud.
.