Hello and welcome to our community! Is this your first visit?
Register
Enjoy an ad free experience by logging in. Not a member yet? Register.
Results 1 to 3 of 3

Thread: count, limit

  1. #1
    Senior Coder
    Join Date
    Dec 2005
    Location
    Slovenia
    Posts
    1,963
    Thanks
    120
    Thanked 76 Times in 76 Posts

    count, limit

    I have a sql that does:
    Code:
    Select ...
    limit 10,20
    I also need to know how many record are there in total, without limit.

    Excluding running another sql, is there a better way to get that ?

    Having something inside stored proc, but alltogether turned out to be super slow,
    as oposed to running only select ... limit.

    EDIT: figured out that sending sql string into stored proc
    and 'executing' it via prepare statement
    tremenduously slows things down !!!


    So, the performance increases tremenduously even if I call db twice (for 2 sql-s) instead.
    Didn't test stored proc without prepare doh.
    Last edited by BubikolRamios; 06-12-2011 at 09:28 PM.
    Found a flower or bug and don't know what it is ?
    agrozoo.net galery
    if you don't spot search button at once, there is search form:
    agrozoo.net galery search

  • #2
    Senior Coder
    Join Date
    Jan 2011
    Location
    Missouri
    Posts
    4,166
    Thanks
    23
    Thanked 601 Times in 600 Posts
    $num_rows = mysql_num_rows($results);
    echo $num_rows;

    Played with this using one query, but it think two queries is the fastest route.

  • #3
    Senior Coder
    Join Date
    Dec 2005
    Location
    Slovenia
    Posts
    1,963
    Thanks
    120
    Thanked 76 Times in 76 Posts
    I have been educated of func SQL_CALC_FOUND_ROWS

    Which looks great (and should work like LAST_INSERT_ID()), if it would work.
    Does not, SELECT FOUND_ROWS() always returns 1 for me.

    At the end the only way that I got it working was like:

    Code:
    Select SQL_CALC_FOUND_ROWS some_field
    FROM  table
    LIMIT 0,18
    
    union
    
    SELECT FOUND_ROWS()
    Dropped the idea.
    Last edited by BubikolRamios; 06-13-2011 at 01:02 AM.
    Found a flower or bug and don't know what it is ?
    agrozoo.net galery
    if you don't spot search button at once, there is search form:
    agrozoo.net galery search


  •  

    Posting Permissions

    • You may not post new threads
    • You may not post replies
    • You may not post attachments
    • You may not edit your posts
    •