Get count of arbitrary mysql select statement in PHP without fetching entire result

Questions

I know there are various functions (in PHP or other programming languages) for getting the number of rows returned by a MySQL query result.

Unfortunately, when querying a very large table, sometimes PHP will run out of memory (for instance when running mysqli_query). Here are the requirements:

1) I know it s a select query, but that s all I know: it may be any query, involving joins, etc. I need a process that isn t query specific.

2) I want to be able to get the number of rows from the result, without fetching the result, since sometimes the result sets are quite large, and I run out of memory in PHP while it fetches the result.

3) I d rather not use buffered results, because I d have to fetch all the buffered results before I get an accurate count (costly).

4) Also unfortunately, I need it to be fairly agnostic to what API (e.g. pdo, mysqli, etc.) is used to fetch the results in PHP since I allow in the app for people to choose which API they ll use.

Thoughts?

Answers

You can take any valid SELECT-type query in any RDMS and wrap it in this:

SELECT COUNT(*) AS num_rows
  FROM (
         /*arbitrary query */
       ) c

It will mostly work. Some RDMSs will protest if you wrap up a query using a LIMIT clause like this.

This returns a one-row resultset containing the row count. Most RDMSs optimize this kind of thing appropriately.

You could use num_rows in your client API. But if you do your RDMS will have to generate, and then abandon, the entire resultset to get that value. That is wasteful of your scarcest resources: time and space on your RDMS server. When the RDMS knows it is counting rows, its query planner can avoid shuffling any data it isn t actually going to need to just count.

Source

License : cc by-sa 3.0

http://stackoverflow.com/questions/34362294/get-count-of-arbitrary-mysql-select-statement-in-php-without-fetching-entire-r

Related