SQL & PHP - Which Is Faster MySQL_Num_Rows() or 'Select Count()'

SQL & PHP - Which is faster mysql_num_rows() or 'select count()'?

mysql_query() transfers all result records from the MySQL into the php pcrocess before it returns (unlike mysql_unbufferd_query()). That alone would make the mysql_num_rows() version slower.

Furthermore for some engines (like MyISAM) MySQL can serve a Count(*) request from the index of the table without hitting the actual data. A SELECT * FROM foo on the other hand results in a full table scan and MySQL has to read every single dataset.

SELECT COUNT() vs mysql_num_rows();

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.

Using count(*) vs num_rows

If your goal is to actually count the rows, use COUNT(*). num_rows is ordinarily (in my experience) only used to confirm that more than zero rows were returned and continue on in that case. It will probably take MySQL longer to read out many selected rows compared to the aggregation on COUNT too even if the query itself takes the same amount of time.

Mysql COUNT(*) and myql_num_rows in php: Which is faster?

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 mysqli_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 scenario:

1) Hey , how many people are in the class room? (count)

2) Hey , get me a list of all the people in the classroom, ... I'll calculate the number of people myself (mysqli_num_rows)

count is the best than mysqli_num_rows

Reference from here.

What is The Difference SELECT COUNT(x) on MySQL vs COUNT(x) on PHP?

Using count on an array in PHP requires these steps:

1. Execute an SQL query
2. Have the database filter the correct data
3. Send all the data from the database over the wire to PHP
4. Have PHP process the data from the database into an array
5. Have PHP count the array

Whereas a COUNT in an SQL query merely does this:

1. Execute an SQL query
2. Have the database count the desired entries using whatever optimisation it can apply (e.g. in-memory indices or previously cached results)
3. Send a single integer from the database over the wire to PHP

The latter is obviously fewer steps, requires wrangling of less data and allows the database to apply more possible optimisations to speed up the answer.

If you only need the count, it's insane not to COUNT in SQL. If you need the count and all the data anyway, then get the data from the database and count it in PHP.

counting rows via php is faster than COUNT in SQL?

You are joinning two tables by yourself. you're an optimizer. you choice 'base' table is outer table for nested loop join. I guess MySQL's optimizer produced execution plan and it was not same as you.

so people want EXPLAIN output to see join order and to check index was used.

by the way, can you try this query?:

SELECT r.x, r.y
FROM `base` AS r, surround AS d
WHERE r.l=50
  AND r.n<>'name'
  AND d.x >= r.x -1
  AND d.x <= r.x +1
  AND d.y>=r.y -1
  AND d.y<=r.y +1
  AND d.n='name'
GROUP BY r.x, r.y
HAVING COUNT(*) = 6

UPDATED

how your original query works

It was first time seeing Range checked for each record (index map: 0x1) so I can't figure out how your query works. MySQL Manual gives us some information about it. It seems like that every row in surround (surround has 57k rows?) is compare to base's x,y. If so, your query is evaluated using 3 depth nested loop join. (base => surround => base) and moreover every row in surround is compared (this is inefficient)

I will make more effort to find how it works later. It's time to work.

Which MySQL query is effective to get the total number of records

I prefer the second query. It gives you already the record count, while the first query gives you the list of IDs (not the count), although it has been filtered but there are some cases when ID exist more than once in the table.

check if row already exists in mysql

Typically you'd want to let MySQL do the counting instead of fetching all rows. MySQL can use indexes and doesn't have to retrieve all data, which may be a lot more efficient:

$result = mysql_query('SELECT COUNT(*) as `count` FROM ...') /* or die(mysql_error()) */;
$row = mysql_fetch_assoc($result);

if ($row['count'] ...)

---

Related Topics