12

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);
Community
  • 1
  • 1
rinchik
  • 2,642
  • 8
  • 29
  • 46
  • As a rule of thumb, the less calls you make to the database the faster your code will be. – Anton Soradoi Oct 12 '12 at 17:56
  • 1
    Rather than `COUNT(id)` (which requires MySQL to inspect whether the `id` of each record is `NULL` and therefore should be excluded from the count), you should use `COUNT(*)`. – eggyal Oct 12 '12 at 17:59
  • @eggyal ID is never null. I'm not sure about the inspection process but i think that its only the case if you will set "ALLOW NULL" for the column. – rinchik Oct 12 '12 at 18:03
  • @rinchik: It's the semantics of `COUNT(expr, ...)` vs `COUNT(*)`. – eggyal Oct 12 '12 at 18:07
  • @AntonSoradoi That's a general rule I'm aware of. There are always some exceptions. – rinchik Oct 12 '12 at 18:19

3 Answers3

47

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.

Your Common Sense
  • 156,878
  • 40
  • 214
  • 345
Matthew
  • 24,703
  • 9
  • 76
  • 110
  • 2
    What if ill get a 1m rows in a results? Memory is not an issue. The question is what would be faster? – rinchik Oct 12 '12 at 18:01
  • Now about my question: Bob already gave me the list of all people. Should i ask Bob how many people in the class room? Or should i use the list that he gave me and count myself? – rinchik Oct 12 '12 at 18:15
  • If you already have the data, then doing a num_rows would be the faster thing to do (as you're not making another query), but you said you're using pagination, so would the num_rows would be a limit of the paged result set, not the total? – Matthew Oct 12 '12 at 18:30
  • I got some results from DB. I have 10 results per page. So i need to count the total amount of pages (count(results)/10) and generate navigation links. Since i already have data from DB i'm asking if i should use (count($query_again)/10) of (mysql_num_rows(results)/10) – rinchik Oct 12 '12 at 19:12
  • 2
    The way I would do it is have two queries. First query to get the total count, then the 2nd to get the paginated data using `LIMIT x OFFSET y`. – Matthew Oct 12 '12 at 19:14
3

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()

CappY
  • 1,510
  • 1
  • 17
  • 32
2

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!

Nigel Ren
  • 56,122
  • 11
  • 43
  • 55
Alireza.A
  • 51
  • 3