On 9/7/06, Paul McCullagh <paul.mccullagh@stripped> wrote:
> It sounds like you program allows ad-hoc queries, so why don't you
> just limit the number of rows returned by a select?
> For example you could limit the number of rows to 1001. If the server
> returns 1001, then display 1000 and tell the user there are actually
> more rows. The user should then apply further conditions.
Some things worth mentioning when using LIMIT:
In MySQL the LIMIT clause is applied just before sending the result to
the client... so a
SELECT col1, col2, ... , colN FROM tableName LIMIT x, y
will be performed as
SELECT col1, col2, ... , colN FROM tableName
and before sending the result to the client the LIMIT will be applied...
There are some things to consider... if you have an ORDER BY clause
MySQL will stop sorting after LIMIT clause is satisfied...
To skit the "COUNT(*)" query you must use:
SELECT SQL_CALC_FOUND_ROWS col1, col2, ... , colN FROM tableName LIMIT x, y
This way MySQL will store internally the number of rows that would
have been returned without the LIMIT clause
[The drawback is that if you have an ORDER BY clause MySQL will not
stop after sorting LIMIT x,y rows... as I mentioned above]
But the gain is that the second query that will return the number of
rows without the LIMIT clause:
will return instantly.
-- -- -- -- -- -- -- -- -- -- -- -- -- --
Senior Web Developer