[RESOLVED] Total rows: which is faster?

Bonesnap

Hello all!

I was creating a function to return the total number of posts in a thread, when I realized I could do it at least two different ways. I'm curious which is the fastest way of doing it.

Method 1

$total_posts = mysql_num_rows(mysql_query("SELECT post_id FROM posts WHERE thread_id = 5"));
return $total_posts;

Method 2

$total = mysql_fetch_assoc(mysql_query("SELECT COUNT(post_id) AS total_posts FROM posts WHERE thread_id = 5"));
return $total['total_posts'];

One returns an integer and the other returns an array with a single value, but is one faster than the other? And if they're pretty much even, which way is considered to be the "better" way?

Thank you in advance for any insight!

dalecosp

//???
return mysql_result(mysql_query("select count(*) from FROM posts WHERE thread_id=5"),0);

Haven't benchmarked it --- but it looks cooler 😃

traq

form of "mysql_num_rows(mysql_query())":
   1000 operations took 0.052248 microseconds
form of "SELECT COUNT(*)":
   1000 operations took 0.052248 microseconds

results varied between .05 and .08, but they were identical 17 times out of 20 executions.
as for coolness, I agree with dalecosp - why select data you don't need?.

NogDog

Although you may want to weigh the millisecond saved by doing that all as a one-liner versus the potential savings of minutes or hours of maintenance time later by splitting it up into more defensive and maintainable code. 😉 (Optimizing the DB query is generally a good thing, as long as it remains reasonably readable, but compressing the PHP code into a one-liner will likely have much less impact on performance than it will on maintainability.)

dalecosp

And, of course, the real b**** here is that mysql_result() is actually probably slower (by the admission of the authors of PHP) than the alternative.

In this case, I'm not sure if there's any difference. I'm with traq ... why get data you don't need, but I see most of the arguments as valid otherwise. I doubt there's much difference between:

$var=foo();
return $var;

and

return foo();

as long as foo() isn't too complex, etc. It could be that you save RAM by not assigning $var, though.

Also, remember, I'm a die-hard Unix lazy typist ... one-liners will always trump unless the one line is truly cryptic 😉

dalecosp

NogDog;10988650 wrote:
Although you may want to weigh the millisecond saved by doing that

Now, here I might get all pedantic and suggest that one millisecond times 10K database records is 10 seconds 😃

Bonesnap

Thanks everyone for your replies! 🙂 I went with the mysql_result method. Also, mysql_result is slower than the alternatives when dealing with a large result set, but in this case I'm retrieving a single row and value, so I doubt it will be slower.

NogDog;10988650 wrote:
Although you may want to weigh the millisecond saved by doing that all as a one-liner versus the potential savings of minutes or hours of maintenance time later by splitting it up into more defensive and maintainable code. 😉 (Optimizing the DB query is generally a good thing, as long as it remains reasonably readable, but compressing the PHP code into a one-liner will likely have much less impact on performance than it will on maintainability.)

I actually find it much easier to read if it's on one line. I read code like a sentence, and really prefer it to be on a single line if possible. I guess I am weird like that. The only exception for me is really complicated IF statements. In that case I'll do some nesting. It's also one of the reasons I've fallen in love with the ternary operator, and I've discovered that the more I become comfortable with it, the more complicated they become. :eek:

NogDog

dalecosp;10988655 wrote:
Now, here I might get all pedantic and suggest that one millisecond times 10K database records is 10 seconds 😃

But you're only running the PHP part of the process once, regardless of the number of records in the DB.

$sql = "SELECT COUNT(*) AS the_count FROM the_table WHERE some_field = 'foo'";
$result = msyql_query($sql);
if($result == false) {
   error_log("Count query failed: " . mysql_error() . "\n$sql");
   my_error_handler("There was a database error, please try again or contact admin.");
   exit;
}
return mysql_result($result, 0);

The PHP part of the above should always take the same amount of time (with the usual variability based on what else the server is doing), regardless of how long it takes to process the actual query due to number of records. If/when the site actually gets the sort of traffic where you really need to worry about the efficiency of your PHP (versus DB queries), you can then throw some caching and/or pre-compilers at it and by optimizing DB reads with something like Sphinx or Mongo.

traq

NogDog;10988658 wrote:
But you're only running the PHP part of the process once, regardless of the number of records in the DB.

well... hopefully 😃