SELECT COUNT() vs mysql_num_rows();

Question

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 1

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.

Solution 2

Use COUNT(id). It only returns the count, With mysql_num_rows($result); php fetch ALL the data from the mysql and count the number of found results.

And finally, don't use mysql_* functions.

Suggested alternatives

Use of this extension is discouraged. Instead, the MySQLi or PDO_MySQL extension should be used. See also MySQL: choosing an API guide and related FAQ for more information. Alternatives to this function include:

mysqli_stmt_num_rows() PDOStatement::rowCount()

Solution 3

Tested in inoDB engine and mysql 5.5.

The id has index and I think this is very fast

$q = "SELECT count(`id`) FROM table where 1";
$rows = mysql_query($q);
$count = mysql_fetch_array($rows);
echo $count[0];

if you want more, you have to use one index just on id or what ever you want to select.

Caching is another solution and you can select from 1 set of records in few milliseconds!