Ken Gieselman <ken@stripped> writes:
> Yeah, that's my "fall-back" option -- though the thought of executing
> the query twice is a bit daunting. Some of the tables run into
> billions of rows per year (the merge tables anyhow, the data tables
> are broken down by month to keep them from becoming totally
> unmanageable), and a multi-year search can take a while to grind out.
There are some optimizations an SQL server can do if it knows it's
only retreiving a count, and not the rows, so it's often not as
computationally expensive as executing the full query twice.
> Seems to me that by the time the first query execution is done, the
> server should *know* exactly how many rows are in the result set --
> just need to find the magic trick that allows the client to query that
> number, rather than counting for itself as it buffers them.
I think in many cases it actually doesn't know; it keeps spitting back
rows until it finds it doesn't have any more. Especially if the query
is complicated enough to require complete records to be inspected, the
server isn't going to keep inspecting records once it's found enough
data to satisfy the current query.
I also think that the COUNT(*) will end up pulling many things into
cache, so the actual results query will be somewhat faster than it
would otherwise be.
Perhaps somebody who knows MySQL internals better than I do can say
for sure whether these are true.