Well we know that in PHP's mysql and mysqli extensions there is a function/method called num_rows(), which returns the total number of rows affected by a SELECT query. This functionality is not available in PDO, the PDOStatement::rowCount() does not return total number of rows from a SELECT query for MySQL.

Although it is possible to count the number of rows using either a SELECT COUNT(*) query, or simply fetch all rows of data as an array and use the count() function on it, these approaches are by no means as efficient and memory-saving than Mysql's internal num_rows() funciton/method.

So I was wondering, what is the best way to simulate a num_row() function/method in PDO? Lemme know if you have any ideas.

    Lord Yggdrasill wrote:

    Although it is possible to count the number of rows using either a SELECT COUNT() query, or simply fetch all rows of data as an array and use the count() function on it, these approaches are by no means as efficient and memory-saving than Mysql's internal num_rows() funciton/method.


    Do you actually have proof of this, or are you just speculating? If you are going to store the rows into an array anyway, I would be surprised to find that a count() on the array would be slower, except maybe by some negligible margin. If you only want a count, I would be surprised to find that the SELECT COUNT(    ) method is slower.

    Lord Yggdrasill wrote:

    So I was wondering, what is the best way to simulate a num_row() function/method in PDO?

    If you are going to actually retrieve all the rows anyway, then just count them yourself. If you only want a count, then SELECT COUNT(*) is appropriate, whatever the database extension.

      To help you out, a while back I ran a benchmark comparing COUNT(*) to num_rows and row_count. (Also in my experience rowCount() works with select on mysql just fine, are you sure you've tried?)

      Testing with a table size of 5000 rows. Each test type (mysql count, mysql num_rows, mysqli count, etc) was run 500 times each.
      Result Set 1:

      Testing COUNT(*) vs num_rows using MySQL, MySQLi, and PDO...
Testing with mysql on localhost...
Creating and filling table... Complete!
Beginning Test of Count(*)... Complete!
Beginning test of num_rows (or similar)... Complete!
MySQL COUNT(*)
	Min:	0.046968 ms
	Max:	1.871109 ms
	Avg:	0.060625 ms
	Median:	0.056028 ms
MySQL num_rows
	Min:	3.751040 ms
	Max:	7.720947 ms
	Avg:	4.073465 ms
	Median:	4.025936 ms
MySQLi COUNT(*)
	Min:	0.046015 ms
	Max:	0.577211 ms
	Avg:	0.055756 ms
	Median:	0.054121 ms
MySQLi num_rows
	Min:	3.568888 ms
	Max:	6.212950 ms
	Avg:	3.795136 ms
	Median:	3.802061 ms
PDO COUNT(*)
	Min:	0.046968 ms
	Max:	1.315832 ms
	Avg:	0.060505 ms
	Median:	0.056028 ms
PDO rowCount()
	Min:	3.883839 ms
	Max:	6.447792 ms
	Avg:	4.139980 ms
	Median:	4.125118 ms
Test complete. Performing cleanup...Complete!

      Result set 2:

      Testing COUNT(*) vs num_rows using MySQL, MySQLi, and PDO...
Testing with mysql on remost host...
Creating and filling table... Complete!
Beginning Test of Count(*)... Complete!
Beginning test of num_rows (or similar)... Complete!
MySQL COUNT(*)
	Min:	39.001942 ms
	Max:	323.285103 ms
	Avg:	67.754380 ms
	Median:	48.503876 ms
MySQL num_rows
	Min:	135.227919 ms
	Max:	1208.184958 ms
	Avg:	313.135995 ms
	Median:	273.215055 ms
MySQLi COUNT(*)
	Min:	38.088799 ms
	Max:	290.810108 ms
	Avg:	65.513473 ms
	Median:	47.893047 ms
MySQLi num_rows
	Min:	212.701082 ms
	Max:	1173.928976 ms
	Avg:	318.914172 ms
	Median:	269.827843 ms
PDO COUNT(*)
	Min:	39.821863 ms
	Max:	379.276037 ms
	Avg:	70.623504 ms
	Median:	48.815012 ms
PDO rowCount()
	Min:	220.091105 ms
	Max:	1131.749868 ms
	Avg:	314.975175 ms
	Median:	276.443005 ms
Test complete. Performing cleanup...Complete!

      As you can see a COUNT(*) statement is by far the most efficient way of getting a row count.

        I see, thanks for showing me the result. Seems that COUNT(*) is the most efficient way here, guess I will need to build a method for my database class that uses this approach then.

          Write a Reply...