Large query results - Limiting the data, but still knowing the full row count - How?

Hi there,

I have query which returns to many results to handle (therefore to be
displayed screen at one time and to be transferred from the server).

I therefore want to create a search results screen which displays a limited
subset of query results and the user of the application can control which
subset of results are displayed (e.g. say there are 10000 matching results,
the user can see results 1-500, and move on in batches of 500).

The only thing is, to get the amount of rows I still have to run a very
similar (and therefore big) MySQL query as if I was getting all 10000
results, just to find out how many results there actually are.

So am I right in thinking this is the way to go about it. Therefore I run
the query once but only ask for a count of results to be returned. This
gives me the total amount of matches.
Then I run the query again, this time returning all the fields information,
but this time I use LIMIT to restrict the actual results.

I hope this question makes sense and thanks in advance for any input.

Re: Large query results - Limiting the data, but still knowing the full row count - How?

On 13 Jun, 10:00, "cluthz" wrote:
> Hi there,
>
> I have query which returns to many results to handle (therefore to be
> displayed screen at one time and to be transferred from the server).
>
> I therefore want to create a search results screen which displays a limited
> subset of query results and the user of the application can control which
> subset of results are displayed (e.g. say there are 10000 matching results,
> the user can see results 1-500, and move on in batches of 500).
>
> The only thing is, to get the amount of rows I still have to run a very
> similar (and therefore big) MySQL query as if I was getting all 10000
> results, just to find out how many results there actually are.
>
> So am I right in thinking this is the way to go about it. Therefore I run
> the query once but only ask for a count of results to be returned. This
> gives me the total amount of matches.
> Then I run the query again, this time returning all the fields information,
> but this time I use LIMIT to restrict the actual results.
>
> I hope this question makes sense and thanks in advance for any input.

Take a look at:
http://dev.mysql.com/doc/refman/5.0/en/information-functions .html#function_found-rows

Re: Large query results - Limiting the data, but still knowing the full row count - How?

"Captain Paralytic" wrote in message any input.
>
> Take a look at:
> http://dev.mysql.com/doc/refman/5.0/en/information-functions .html#function_found-rows

Ahh, looks perfect. Thank you!

Re: Large query results - Limiting the data, but still knowing the full row count - How?

"cluthz" wrote in message news:TMPbi.4333>>
>> Take a look at:
>> http://dev.mysql.com/doc/refman/5.0/en/information-functions .html#function_found-rows
>
> Ahh, looks perfect. Thank you!

Actually on looking a bit more into this it seems the method described has
some bugs with it that do not actually make it faster in many cases then
running the query twice.

I found the following postings on the subject:
http://bugs.mysql.com/bug.php?id=19553

and
http://bugs.mysql.com/bug.php?id=18454

I did not fully understand the bug process yet, but it seems to me the bug
is still open and the text has made me question whether I bother changing my
code to use something which may slow down my results, especially as I'm a
bit pressured for time and don't have the luxury of benchmarking etc.

Thanks for any more input.

Re: Large query results - Limiting the data, but still knowing the full row count - How?

In fact, I Cannot even get it to work when using a simple query from PHP
myAdmin.

For example, I try and enter the following basic sql query and get the
following MySQL error (using sql 4).

SQL query:

SELECT SQL_CALC_FOUND_ROWS , arbitratry_field
FROM mytable
LIMIT 0 , 30

MySQL said:

#1064 - You have an error in your SQL syntax; check the manual that
corresponds to your MySQL server version for the right syntax to use near '
ac_arbitratryFROM mytable
LIMIT 0, 30' at line 1

What is a basic example that should work or am I doing something obviously
wrong?

Thanks again.

Re: Large query results - Limiting the data, but still knowing the full row count - How?

On 17 Jun, 13:57, "cluthz" wrote:
> In fact, I Cannot even get it to work when using a simple query from PHP
> myAdmin.
>
> For example, I try and enter the following basic sql query and get the
> following MySQL error (using sql 4).
>
> SQL query:
>
> SELECT SQL_CALC_FOUND_ROWS , arbitratry_field
> FROM mytable
> LIMIT 0 , 30
>
> MySQL said:
>
> #1064 - You have an error in your SQL syntax; check the manual that
> corresponds to your MySQL server version for the right syntax to use near '
> ac_arbitratryFROM mytable
> LIMIT 0, 30' at line 1
>
> What is a basic example that should work or am I doing something obviously
> wrong?
>
> Thanks again.

A basic example that should work can be found at the link I sent you
in my post!

What you are doing wrong is putting a comma where the working example
does not have one.

Re: Large query results - Limiting the data, but still knowing the full row count - How?

"Captain Paralytic" wrote in message >
> A basic example that should work can be found at the link I sent you
> in my post!
>
> What you are doing wrong is putting a comma where the working example
> does not have one.


OK great I'll try this out. any opinion on what those other links I sent say
about it not being as efficient as running two sql queries in some cases?

Re: Large query results - Limiting the data, but still knowing the full row count - How?

On 19 Jun, 02:06, "cluthz" wrote:
> "Captain Paralytic" wrote in message >
> > A basic example that should work can be found at the link I sent you
> > in my post!
>
> > What you are doing wrong is putting a comma where the working example
> > does not have one.
>
> OK great I'll try this out. any opinion on what those other links I sent say
> about it not being as efficient as running two sql queries in some cases?

As with pretty much everything, it is a case of "horses for courses".
You always need to look at what you need and decide on the best way of
accomplishing it in that particular case. Depending on various
criteria, I sometimes use this method in my apps and sometimes, where
there are different criteria, I use separate queries. There are always
many ways to "skin a cat", the programmer's challenge is to choose the
best one in the circumstances.

Re: Large query results - Limiting the data, but still knowing the full row count - How?

Post removed (X-No-Archive: yes)