Select Count() VS MySQL_Num_Rows();

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.

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.

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

Using count on an array in PHP requires these steps:

Execute an SQL query
Have the database filter the correct data
Send all the data from the database over the wire to PHP
Have PHP process the data from the database into an array
Have PHP count the array

Whereas a COUNT in an SQL query merely does this:

Execute an SQL query
Have the database count the desired entries using whatever optimisation it can apply (e.g. in-memory indices or previously cached results)
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.

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.

MySQL equivalent of mysql_num_rows. Which is better?

I wouldn't say it is "a good alternative". It is something completely different.

    SELECT data FROM table

fetches data, that you can use. You can then use mysql_num_rows() (or rowCount() in the PDO case) to get the number of records (for rendering, pagination or whatever).

If you only want the number of records (and not the actual data), you should use

    SELECT COUNT(data) FROM table

as you are only selecting the number of records (to be precise, you are counting non-NULL values of data, use SELECT COUNT(*) to get all records).

If you would be using the latter in combination with the former to get both, you could get into trouble when the number of records change between the two queries.

How do I SELECT and COUNT rows in SQL?

$result = mysql_query( "SELECT * FROM table");
$count = mysql_num_rows( $result);

Using PDO:

$statement = $dbh->prepare('SELECT * FROM table');
$statement->execute();
$count = $statement->rowCount();

c# and SQL server's equivalent to mysql_num_rows in PHP

System.Data.SqlClient

SqlDataReader has the RecordsAffected Property to get that value, except for select statements. Therefore you have to count manually or use a SELECT COUNT(*) in your SQL statement.

EntityFramework

Have a look a this article.

LINQ2SQL

var pupilCOunt = (from p in schoolContext.Pupils select p).Count();

How to do a num_rows() on COUNT query in codeigniter?

Doing a COUNT(*) will only give you a singular row containing the number of rows and not the results themselves.

To access COUNT(*) you would need to do

$result = $query->row_array();
$count = $result['COUNT(*)'];

The second option performs much better since it does not need to return a dataset to PHP but instead just a count and therefore is much more optimized.

Select Count() VS MySQL_Num_Rows();