best practice to count record in MySQL + PHP

Question

I want to check weather number of record > 15 for a condition.

What is best practice to speed up the query?

1)

$query="SELECT `id` FROM `table` WHERE `name`='$name' AND `usage` > '$limit'";
$result=mysql_query($query);       
if(mysql_num_rows($result) >15) 
{
    //do task

}

2)

$query="SELECT COUNT(1) AS 'cnt' FROM `table` WHERE `name`='$name' AND `usage` > '$limit'";

$result=mysql_query($query); 
$row = mysql_fetch_assoc($result);

if($row['cnt'] >15) 
{
    //do task

}

3) any other best way?

[`MySQL`](http://php.net/manual/en/book.mysql.php) (`mysql_*` functions) extension is [***deprecated***](http://php.net/manual/en/function.mysql-connect.php). I suggest to use [`MySQLi`](http://php.net/manual/en/book.mysqli.php) (`mysqli_*` functions) or [`PDO`](http://php.net/manual/en/book.pdo.php) instead. — BlitZ, May 22 '13 at 10:34
What was faster/more efficient when you looked at the EXPLAIN plans and when you tested it? — symcbean, May 22 '13 at 11:14
possible duplicate of [my sql best practice with php for counting rows](http://stackoverflow.com/questions/12189147/my-sql-best-practice-with-php-for-counting-rows). Another [interesting resource here](http://stackoverflow.com/questions/1761215/count-from-a-table-but-stop-counting-at-a-certain-number). — RandomSeed, May 22 '13 at 15:51

score 5 · Accepted Answer · answered May 22 '13 at 10:36

5

First, use COUNT( * ) instead of COUNT(fieldname) since mysql is optimised for COUNT( * ).

Second, if the table is very large then ensure that you have indexes on the columns in the WHERE clause to speed up execution.

answered May 22 '13 at 10:36

Rijndael

3,683
2
24
26

thank you for the reply. I only check if no of record in >15. so i think it is best to put LIMIT 16 in the query – user2409005 May 22 '13 at 13:19

score 0 · Answer 2 · answered May 22 '13 at 10:36

0

u can use

$query="SELECT COUNT(id) as cnt FROM `table` WHERE `name`='$name' AND `usage` > '$limit'";

$result=mysql_query($query); 
$row = mysql_fetch_assoc($result);

if($row['cnt'] >15) 
{
    //do task

}

answered May 22 '13 at 10:36

sAnS

1,169
1
7
10

score 0 · Answer 3 · edited May 23 '17 at 11:50

Try this,

$query="SELECT SQL_CALC_FOUND_ROWS `id` FROM `table` WHERE 
         `name`='$name' AND `usage` > '$limit'";
$result=mysql_query($query);       
if(mysql_num_rows($result) >15) 
{
    //do task
}

For total rows, use

$resultTotal=mysql_query("SELECT FOUND_ROWS()");

Read this http://dev.mysql.com/doc/refman/5.0/en/information-functions.html#function_found-rows

also Which is fastest? SELECT SQL_CALC_FOUND_ROWS FROM `table`, or SELECT COUNT(*)

argentum47 · Answer 4 · 2013-05-22T11:00:09.307

0

you can check it yourself for both the codes you posted in this fashion:

$mt=microtime(true);
/* your code */
$qt=microtime(true)-$mt;
print "time elapsed: " . number_format($et) . "s\n";

i think for a single time use the second one would be economic but if u have to iterate over the values a prepared statement will be better.

edited May 22 '13 at 11:00

answered May 22 '13 at 10:45

argentum47

2,385
1
18
20

score 0 · Answer 5 · answered May 22 '13 at 11:14

Obviously, second is better. at first query, Mysql try to consider some system resources for fetching and keeping the id column as a result set like $result, specially in a large amount of data, it will be an important reason for getting more time to execute query. But at the second query , it just assign a counter variable like cnt for showing counting records.

Also note, COUNT(id) does not count NULL values. Then if id can be set to NULL by definition, it's better to use COUNT(*) or based on your choice to counting NULL value or not.

best practice to count record in MySQL + PHP

5 Answers5