creating an efficient pagination system

Question

So i'm creating a pagination system for a blog-like system. I already have the whole concept behind limiting the query using limit,offset. However, I came to a problem when creating the actual navigation.

I have no idea how to calculate the amount of pages I have total. Now, obviously I can run a query that simply selects the count of all the rows, however, I feel as if that is not the most efficient way to go about doing it.

Is there anyway I can pull to total rows in my original query?

 $this->DB->build(  array('select'   => 'a.*', 
                     'from'     => array( 'ccs_custom_database_1' => 'a' ),
                      'order' => 'field_3 DESC',
                      'where' => 'field_6=",1,"',
                      'limit' => array($page,10),

                     'add_join'  => array(
                    array(
                        'select'    => 'm.members_display_name,m.members_seo_name',
                        'from'      => array( 'members' => 'm' ),
                        'type'      => 'inner',
                        'where'     => 'a.member_id=m.member_id'


                    ), 
                    array(
                        'select'    => 'c.category_name,c.category_furl_name',
                        'from'      => array( 'ccs_database_categories' => 'c' ),
                        'type'      => 'inner',
                        'where'     => 'a.category_id=c.category_id'            
                    )),

                    ));

Answer 1

If your table uses the MyISAM engine, running COUNT(*) is an O(1) operation as it keeps a cached value of the total number of rows.

Even if you weren't using that, or had a slightly different query, I doubt that doing a single COUNT would be a bottleneck in the system.

If it really were a huge issue (and I would suggest testing on a table with an amount of fake data that you would expect first), one option is to take a slightly more pragmatic approach to it. It might be different for your users in your use case, however, accept that no one really uses pagination anyway. At most, you could expect someone to go through 2 or 3 pages before giving up. Use filtering and sorting to push the data the user wants up to the first page.

However, even with filtering and sorting, you'd still need to show some sort of pagination probably, so you could take this approach: Given a page size of 15 records, select the first 151 (15 per page * 10 pages + 1) records and count how many you get. If the number is less than that, then show "Page 1 of 8" or whatever. If the number is 151, then you have more than 10 pages, so show "Page 1 of lots". This is an approach I used on a table which had ~12 million records and it worked very well.

Answer 2

You have 2 options.

1. Use an query selecting the total count.
2. Use FOUND_ROWS().

I prefer the second option, but in matter of performance it matters little.