MySQL FOUND_ROWS() when using LIMIT

[whostheJBoss]

I am running a large query that uses the LIMIT keyword for pagination.

Since LIMIT only returns a range of results, it does not give the
total count of the record set, only the LIMIT count.

So, if my query has 500 results, but I'm showing 20 at a time with
LIMIT, the record count is 20.

I used SQL_CALC_FOUND_ROWS after my select statement and then both:

SET @rows = FOUND_ROWS();

and

SELECT FOUND_ROWS();

after my first query, but I get the following, respectively:

Application Execution Exception
Error Type: database : 0
Error Messages: 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 'SET @rows = FOUND_ROWS()' at line 77

and:

Application Execution Exception
Error Type: database : 0
Error Messages: 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 'SELECT FOUND_ROWS()' at line 78

I am probably slaughtering my SQL or something of that nature, but
I'll post a sample query:

I am doing things according to:

http://www.arraystudio.com/as-workshop/mysql-get-total-number-of-rows-when-using-limit.html
http://dev.mysql.com/doc/refman/5.0/en/information-functions.html

Some PHP users are reporting that there are PHP settings that interfer
with this execution, I didn't know if Railo might hav something
similar going on:

http://stackoverflow.com/questions/674061/sqlcalcfoundrows-foundrows-does-not-work-in-php

By the way, if anyone has a good suggestion for pagination on
thousands of rows coming out of a database of millions of records, I'm
open to ideas!

Anyway, I'm betting that this is me using the wrong table type or
something (although I'm using innoDB), or maybe I'm missing the
concept, but it seems as though I'm doing it correctly.

If it's not a Railo thing, I'll take it to the appropriate forums, but
I'd like a little feedback if anyone else has experienced or tried
this. Thanks!

[Barney Boisvert]

MySQL only allows a single statement per query by default. So you'll
need to do this:

<cftransaction>
<cfquery ...>
select SQL_CALC_FOUND_ROWS ....
</cfquery>
<cfquery ...>
select found_rows() as foundRows
</cfquery>
</cftransaction>

I know you can enabled multiple statements per query with the driver
config, but I've never actually done it. Not sure how it handles
multiple results sets coming back from the same query. The
cftransaction isn't really there for transactionality, it's just to
ensure that you're on the same connection for both queries. CF will
ensure this at the request level anyway, but BlueDragon does not. I'm
not sure about Railo (haven't tested).

cheers,
barneyb

--
Barney Boisvert
bboi...@gmail.com
http://www.barneyb.com/

[Judah McAuley]

I think one of the problems here is that you're trying to retrieve two
different sets of data in one cfquery which is not something you can
do (in most cases).

According to this:
http://www.arraystudio.com/as-workshop/mysql-get-total-number-of-rows-when-using-limit.html

You do two sql queries back to back thusly:

SELECT SQL_CALC_FOUND_ROWS name, email FROM users WHERE name LIKE 'a%' LIMIT 10;

SELECT FOUND_ROWS();

The problem being that Railo won't want to give you back two
recordsets in one cfquery. If you want to do it in that fashion, I
think you'll need to create a stored procedure so you can use
variables (as you seem to be trying to do) and return one or more data
sets. I haven't tried cfstoredproc under Railo though, so I'm not sure
how well it is supported.

Judah

On Mon, Jan 11, 2010 at 6:15 PM, whostheJBoss
<dotf...@changethings.org> wrote:

[whostheJBoss]

Thanks Barney!

Railo handled this beautifully! The main query executed with LIMIT in
6ms, the result count came in at 0ms and gave the proper number. This
is very handy!

Combine this with good a turbo-charged IBO and you could get dangerous
results :)

Thanks again!

> >http://www.arraystudio.com/as-workshop/mysql-get-total-number-of-rows...

> >http://dev.mysql.com/doc/refman/5.0/en/information-functions.html
>
> > Some PHP users are reporting that there are PHP settings that interfer
> > with this execution, I didn't know if Railo might hav something
> > similar going on:
>

> >http://stackoverflow.com/questions/674061/sqlcalcfoundrows-foundrows-...

>
> > By the way, if anyone has a good suggestion for pagination on
> > thousands of rows coming out of a database of millions of records, I'm
> > open to ideas!
>
> > Anyway, I'm betting that this is me using the wrong table type or
> > something (although I'm using innoDB), or maybe I'm missing the
> > concept, but it seems as though I'm doing it correctly.
>
> > If it's not a Railo thing, I'll take it to the appropriate forums, but
> > I'd like a little feedback if anyone else has experienced or tried
> > this. Thanks!
>
> --
> Barney Boisvert

> bboisv...@gmail.comhttp://www.barneyb.com/

[whostheJBoss]

Anywhere from 8 -12ms on CF9 (occasionally 7 and occasionally up to 23
on CF), with the first time being 89ms.

First was 6ms on Railo, 6 or 5 every other time. :)

[Judah McAuley]

Barney always beats me to the punch on these sorts of things and with
more elegant solutions. Not sure why I thought of a storedproc but not
cftransaction. Nicely done.

Judah

[whostheJBoss]

Hey Barney,

Do you know of a good way to test whether or not Railo ensures this at
the request level? The transaction is causing a major problem in my
application, so I need to remove it. I just want to make sure I don't
cause any freaky results.

Thanks!

On Jan 11, 10:56 pm, Barney Boisvert <bboisv...@gmail.com> wrote:

> MySQL only allows a single statement per query by default.  So you'll
> need to do this:
>
> <cftransaction>
> <cfquery ...>
>   select SQL_CALC_FOUND_ROWS ....
> </cfquery>
> <cfquery ...>
>   select found_rows() as foundRows
> </cfquery>
> </cftransaction>
>
> I know you can enabled multiple statements per query with the driver
> config, but I've never actually done it.  Not sure how it handles
> multiple results sets coming back from the same query.  The
> cftransaction isn't really there for transactionality, it's just to
> ensure that you're on the same connection for both queries.  CF will
> ensure this at the request level anyway, but BlueDragon does not.  I'm
> not sure about Railo (haven't tested).
>
> cheers,
> barneyb
>
> On Mon, Jan 11, 2010 at 6:15 PM, whostheJBoss
>
>
>
>
>
> <dotfus...@changethings.org> wrote:
> > I am running a large query that uses theLIMITkeyword for pagination.
>

> > SinceLIMITonly returns a range of results, it does not give the

> > total count of the record set, only theLIMITcount.
>
> > So, if my query has 500 results, but I'm showing 20 at a time with
> >LIMIT, the record count is 20.
>
> > I used SQL_CALC_FOUND_ROWS after my select statement and then both:
>
> > SET @rows = FOUND_ROWS();
>
> > and
>
> > SELECT FOUND_ROWS();
>
> > after my first query, but I get the following, respectively:
>
> > Application Execution Exception
> > Error Type: database : 0
> > Error Messages: 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 'SET @rows = FOUND_ROWS()' at line 77
>
> > and:
>
> > Application Execution Exception
> > Error Type: database : 0
> > Error Messages: 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 'SELECT FOUND_ROWS()' at line 78
>
> > I am probably slaughtering my SQL or something of that nature, but
> > I'll post a sample query:
>
> > I am doing things according to:
>

> >http://www.arraystudio.com/as-workshop/mysql-get-total-number-of-rows...

> >http://dev.mysql.com/doc/refman/5.0/en/information-functions.html
>
> > Some PHP users are reporting that there are PHP settings that interfer
> > with this execution, I didn't know if Railo might hav something
> > similar going on:
>

> >http://stackoverflow.com/questions/674061/sqlcalcfoundrows-foundrows-...

>
> > By the way, if anyone has a good suggestion for pagination on
> > thousands of rows coming out of a database of millions of records, I'm
> > open to ideas!
>
> > Anyway, I'm betting that this is me using the wrong table type or
> > something (although I'm using innoDB), or maybe I'm missing the
> > concept, but it seems as though I'm doing it correctly.
>
> > If it's not a Railo thing, I'll take it to the appropriate forums, but
> > I'd like a little feedback if anyone else has experienced or tried
> > this. Thanks!
>
> --
> Barney Boisvert

> bboisv...@gmail.comhttp://www.barneyb.com/

[Barney Boisvert]

If a transaction is causing a problem, then you've some other issue,
so I'd figure that one out. The request-scoping nature of
transactions (present or not) is an implementation detail; you can't
assume it'll remain constant across versions. You could build a
couple test scripts that you can run concurrently to see if you get
request-scoped connections, but that's not guaranteed because the
behaviour of Railo might change under load (i.e., when the connection
pool is tapped, it might free/reuse connections in a more aggressive
fashion).

cheers,
barneyb

--