Search code examples
phpmysqlselectoptimizationlarge-data

SELECT COUNT() vs mysql_num_rows();

I have a large table (60+) millions of records.

I'm using PHP script to navigate through this table.

PHP script (with pagination) loads very fast because:

The table engine is InnoDB thus SELECT COUNT() is very slow and mysql_num_rows() is not an option, so i keep the total row count (the number that i use to generate pagination) in a separate table (i update this record total_rows=total_rows-1 and total_rows=total_rows1+1 during DELETE and INSERT).

But the question is what to do with the pagination for search results?

Right now I'm doing this with 2 steps:

1.

$condition = " fname='rinchik' ";
$result = "SELECT * FROM my_large_table WHERE" . $condition;

Here i got all search results from DataBase.

2. Now i need to count these results to create pagination. I'm doing this:

$condition; <- we already have this from the step 1
$result_count = "SELECT COUNT(id) FROM my_large_table WHERE" . $condition;

And it's kinda slow.

Would it be better if i will do it this way (with just one step)?:

$condition = " fname='rinchik' ";
$result = "SELECT * FROM my_large_table WHERE" . $condition;
$result_count = mysql_num_rows($result);
Solution

  • Use COUNT, internally the server will process the request differently.

    When doing COUNT, the server will only allocate memory to store the result of the count.

    When using mysql_num_rows, the server will process the entire result set, allocate memory for all those results, and put the server in fetching mode, which involves a lot of different details, such as locking.

    Think of it like the following pseudo scenarios:

    SELECT COUNT(*)

    Hey Bob, how many people are in the class room?

    mysql_num_rows

    Hey Bob, send all the people from the classroom over to me, ... I'll count them to get the number of people myself

    In summary, when using mysql_num_rows you are transferring all records to the client, and the client will have to calculate the count itself.