How to count and limit record in a single query in MYSQL?

Question

I am searching for records in a table as follows:

SELECT Id, Name FROM my_table WHERE Name LIKE '%prashant%' LIMIT 0, 10;

Now, I am adding LIMIT to maintain my paging. But when user searches for word 'prashant' then total records I have is 124 for 'prashant'. But as the limit applied to the query so it only fetches 10 records in my PHP script and when I am count the mysql variable in PHP code it returns total records found is 10.

So basically I want to count and Limit using a single query, by making some modification in the above query, I want the total count (124) of records. I don't want to run a separate count(*) query for just counting the total result found by the query.

Thanks.

Solution 1

SELECT SQL_CALC_FOUND_ROWS
  Id, Name
FROM my_table
WHERE
  Name LIKE '%prashant%'
LIMIT 0, 10;

# Find total rows
SELECT FOUND_ROWS();

more info

Solution 2

MySQL supports doing this using SQL_CALC_FOUND_ROWS as mentioned by Ionut. However, it turns out that in many cases it's actually faster to do it the old fashioned way using two statements, where one of them is a regular count(). This does however require that the counting can be done using an index scan, which won't be the case for this very query, but I thought I'd mention it anyway.

Solution 3

This is for others with the same need (considering it's been 3 years from the time of this question).

I had a similar issue and I didn't want to create 2 queries. So what I did was to create an additional column for the total number and moved the LIMIT and OFFSET clauses at the end:

SELECT SQL_CALC_FOUND_ROWS * FROM (
    SELECT `id`, `name`
    FROM `my_table`
    WHERE `name` LIKE '%prashant%'
) res,
(SELECT /*CEIL(FOUND_ROWS()/10) AS 'pages',*/ FOUND_ROWS() AS 'total') tot
LIMIT 0, 10

So the result is something like

| id  |      name      | total |
+-----+----------------+-------+
|  1  | Jason Prashant |  124  |
|  2  | Bob Prashant   |  124  |
|  3  | Sam Prashant   |  124  |
|  4  | etc. prashant  |  124  |

and I think this solution has no disadvantage in timing because it fetches the result only once, and then uses the already calculated row count for the additional column.

Solution 4

In case of huge tables and selecting multiple fields (not just Id, Name as in your example) i would recommend to use 2 queries. Selecting count(0) first with all those WHERE clauses and only then build the pagination, selecting data etc. It will work really faster on some popular eCommerce website, for example.

View more solutions