Hi Ken,
On Sep 18, 2005, at 3:53 PM, Ken Gieselman wrote:
Scott Gifford wrote:
Ken Gieselman <[EMAIL PROTECTED]> writes:
[...]
So, here's my question: How can I find out how many rows are being
returned by a query, without using mysql_stmt_store_result() to
buffer all the rows back to the client?
One straightforward way is to replace the SELECT clause with just
SELECT COUNT(*) and execute that query first.
-----ScottG.
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.
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.
The problem is that your assumption isn't always correct. The server
doesn't always know how many rows are in a result set before it starts
returning rows back to the client. Imagine if you did a simple SELECT
* FROM tbl WHERE unindexed_col = 5; statement. The server certainly
isn't going to read in and count the number of rows before beginning to
send the rows back to the client, for the same reason that you don't
want to do a store_result, it will take up too much resources. The
only way for the server to know 100%, is to count the rows as they are
being returned to the client (It could in theory do it for some
statements, such as where it has to do a filesort, however an API that
only sometimes worked based on the execution plan wouldn't be very
useful) .
Regards,
Harrison
--
Harrison C. Fisk, Trainer and Consultant
MySQL AB, www.mysql.com
Get a jumpstart on MySQL Cluster --
http://www.mysql.com/consulting/packaged/cluster.html
Regards,
Harrison
--
Harrison C. Fisk, Trainer and Consultant
MySQL AB, www.mysql.com
Mobile: +1 315 380-6048
Get a jumpstart on MySQL Cluster --
http://www.mysql.com/consulting/packaged/cluster.html
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
