Harrison Fisk wrote:
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
That makes a fair amount of sense :) I guess I'd just envisioned it as having a
list of pointers to matches or something similar internally. OK, given the
limitations, is there an effective method to *estimate* what's likely to come
back? I've played a bit with pulling the numbers out of an EXPLAIN SELECT...
but it seems that they tend to come in low, if the query is using indexes, or
high (all rows) if not.
Appreciate the input guys! Thanks a bunch.
ken
--
===========================================================================
"Diplomacy is the weapon of the Civilized Warrior"
- Hun, A.T.
Ken Gieselman [EMAIL PROTECTED]
Endlessknot Communications http://www.endlessknot.com
===========================================================================
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
