List:General Discussion« Previous MessageNext Message »
From:Gabriel PREDA Date:September 10 2006 8:22am
Subject:Re: problem with InnoDB
View as plain text  
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:
SELECT FOUND_ROWS()
will return instantly.


-- -- -- -- -- -- -- -- -- -- -- -- -- --
Gabriel PREDA
Senior Web Developer
Thread
RE: problem with InnoDBprasad.ramisetti4 Sep
  • Re: problem with InnoDBDan Nelson4 Sep
RE: problem with InnoDBprasad.ramisetti7 Sep
  • Re: problem with InnoDBChris7 Sep
  • Re: problem with InnoDBDouglas Sims7 Sep
  • Re: problem with InnoDBDan Nelson7 Sep
    • Re: problem with InnoDBDouglas Sims7 Sep
RE: problem with InnoDBprasad.ramisetti7 Sep
  • Re: problem with InnoDBJochem van Dieten7 Sep
RE: problem with InnoDBprasad.ramisetti7 Sep
  • Re: problem with InnoDBPaul McCullagh7 Sep
    • Re: problem with InnoDBGabriel PREDA10 Sep