CODEWITHSUNDEEP
phpmysql
panthro
panthro

Reputation: 24061

Counting all rows with a limit

I need to filter (using where clauses) the rows in my table, count the total number of rows from this filter and then limit number of rows for pagination.

What is quickest/more efficient?

  1. Count the rows with sql query. Select the rows with a limit with sql query.
  2. Select all rows with sql query. Count the array with PHP. Split the array with PHP.

Or is there another way to count all rows and get a limited set of the results out?

Upvotes: 1

Views: 2587

Answers (3)

Sarath Chandra
Sarath Chandra

Reputation: 1878

The MySQL LIMIT should do the trick for you. This is a more efficient approach, as it helps in fetching only the relevant records to be displayed and nothing else.

Note that the startingIndex, numberOfRecordsPerPage variables should be set before executing the query.

SELECT * FROM MY_TABLE where 
(...) //ALL CONDITIONS go here
LIMIT startingIndex, numberOfRecordsPerPage;

From the MySQL Reference

With two arguments, the first argument specifies the offset of the first row to return, and the second specifies the maximum number of rows to return. The offset of the initial row is 0 (not 1):

SELECT * FROM tbl LIMIT 5,10; # Retrieve rows 6-15

In order to identify whether or not to expect any records in return, one must first run the query (with COUNT and without any LIMIT condition):

`SELECT COUNT(*) FROM MY_TABLE where (...) //ALL CONDITIONS go here`

and store it separately. This shall be the total records applicable to the WHERE clauses given.

If the sum startingIndex+numberOfRecordsPerPage is >= TOTAL_COUNT then that paginated set shall be the last of the whole list and the user should not be allowed to click the NEXT button.

PS: Also, as pointed out in the comments, you might want to look at a framework alternative, like Criteria API for Java, to the heavy weightlifting, particularly if your WHERE conditions are also generated dynamically.

Upvotes: -1

Danijel
Danijel

Reputation: 12689

Use two queries, one to retrieve total number of rows, another to get the rows. The first argument in LIMIT clause specifies the offset of the first row ( current_page * post_per_page ), and the second specifies the maximum number of rows to return ( post_per_page ).

SELECT COUNT(*) AS num_rows FROM table;

SELECT * FROM table LIMIT start, length;

Upvotes: 0

doublesharp
doublesharp

Reputation: 27599

You should use SQL_CALC_FOUND_ROWS and the FOUND_ROWS() function to return the total number of rows even when a LIMIT is applied to the returned results. After you run your query the results will be returned, and then you can run SELECT FOUND_ROWS() to return the total number of rows without having to run the query again.

SELECT SQL_CALC_FOUND_ROWS * FROM my_table WHERE column = 'something' LIMIT 10;
SELECT FOUND_ROWS() AS total_results;

Upvotes: 0

Related Questions