hakkr has asked for the wisdom of the Perl Monks concerning the following question:

I use the Mysql LIMIT clause to return the required number of records( X to Y from total) for a results list page. LIMIT however sets $qh->rows to the number of rows you specify so I have to do a another query omitting the LIMIT clause to get the real total. This means all searchs take 2 queries as below

return first 10 recs and total =10
SELECT field FROM table WHERE text='test' LIMIT 1,10

returns the real total
SELECT count(*) FROM table WHERE text='test'

Is there a way to use LIMIT and get the real total in 1 query or am I better off dumping LIMIT altogether and going back to using array slices. Array slices are slightly more complicated and also seem flawed because I'm returning a lot of data from the db that is just thown away.

Roll on Mysql 4 subselects.
cheers

Replies are listed 'Best First'.
Re: Theres no LIMIT
by Masem (Monsignor) on Dec 14, 2001 at 17:05 UTC
    I'd keep it as you use above. Typically, a lot of time is spent in DBI calls in the process of translating the data from the database's format to the internal structure used by perl's DBI. If, to just figure the size of the data, you used a "SELECT *" SQL call, you'd have to wait for the system to put the entire database into perl memory before you get that value. Unless the database was rather small , splitting your SQL up into these two separate pieces will be faster (since you only have to convert 10 rows + 1 data piece) than converting N rows altogether.

    -----------------------------------------------------
    Dr. Michael K. Neylon - mneylon-pm@masemware.com || "You've left the lens cap of your mind on again, Pinky" - The Brain
    "I can see my house from here!"
    It's not what you know, but knowing how to find it if you don't know that's important

[reply]
Re: Theres no LIMIT
by dug (Chaplain) on Dec 14, 2001 at 19:29 UTC
    In mysql 4.0 there are two new functions that are implemented to take care of this from a database processing perspective. The first is SQL_CALC_FOUND_ROWS, and the second is FOUND_ROWS().

    SELECT SQL_CALC_FOUND_ROWS field FROM table WHERE text='test' LIMIT 1,10;

    gives you the 10 rows

    select FOUND_ROWS();

    now gives you the total number of rows that would have been matched without the LIMIT in the first query. It's still two queries, but they are much more efficient.
[reply]

Back to Seekers of Perl Wisdom