Pagination, Count to calculate limit and offset by MYSQL

Asked

Viewed 1,806 times

3

I have a query with 7 relationships in a relatively large table, and the list of records uses the total of records as the basis, so it would be a query to calculate the total and another to display the records between the limit and offset.

PHP calculation for the offset...

$perpage = 10
$current = 1;
$records = 100; // select count()...
$average = ceil( $records / $perpage );
$offset  = ($current > $average) ? $perpage * ( $average - 1 ) : $perpage * ( $current - 1 );

I do not know the background of MYSQL operations, and only find the total being done via query.

I was wondering if there is any way to make MYSQL do the calculation of $offset without having to execute a query just for that?

mysql
  • 2

    For a long time now, like you, good question +1.

  • 1

    @Jorgeb., For small cositas I never had problem, but run 2 querys practically identical and large, I believe that is not the best option. I want to optimize this, because just looking gave me agony :)

  • I think I could bring the total in the same query using a subquery, but besides being kind of weird, I don’t know if it would bring any improvement in performance.

1 answer

3


I’ve been looking for a solution and found sql_calc_found_rows and found_rows().

In the example I used in the question, basically it makes the calculation based on the total number of records found and validates the current page to see if it is larger than the total number of pages calculated. However this can be circumvented using the result of found_rows() subsequently.

** QUERY 1
select sql_calc_found_rows id from TABELA limit ? offset ?
bindValue( $perpage )
bindValue( ( ( $current - 1 ) * $perpage ) )

** QUERY 2
select found_rows() as total

Output

Array
(
    [0] => Array( [id] => 4 )
    [1] => Array( [id] => 5 )
    [2] => Array( [id] => 6 )
)

Array
(
    [0] => 16
)

Handbook

mysql> SEELCT SQL_CALC_FOUND_ROWS * FROM tbl_name
    -> WHERE id> 100 LIMIT 10; 
mysql> SEELCT FOUND_ROWS();

1) The second select returns a number indicating how many lines the first select would have returned if it had been written without the LIMIT clause.

2) If you are using SELECT SQL_CALC_FOUND_ROWS, Mysql should calculate how many records there are in the complete result set. However, this is faster than executing the query again without LIMIT, because the result set does not need to be sent to the client.

3) SQL_CALC_FOUND_ROWS and FOUND_ROWS() may be useful in situations where you want to restrict the number of rows a query returns, but also determine the number of rows in the complete result set without running the query again. An example is a web script that displays a paged display containing links to pages that show other sections of a search result. Using FOUND_ROWS() allows you to determine how many other pages are required for the rest of the result.

Browser other questions tagged mysql

You are not signed in. Login or sign up in order to post.