Count

[jaymc]

I have a query that queries a table and pulls out the last 5 comments posted

 

I need it to also say how many comments there are in total

 

At current the first query has a LIMIT 0,5 si I cant use mysql_num_rows()

 

And, I cant take the limit out because the query needs to be optimised

 

Will I need a seperate query just to tell me how many comments all together, or is there a way to intergreat a function into the query such as COUNT()

 

Cheers

[soycharliente]

I would just use COUNT() and then when you pull out the comments (in your while loop?) do the limiting there with some counter that you increment.

[jaymc]

I was going to so that.. while($i < 5 mysql_fetch_array())

 

However, its going to be putting all of the rows in that fetch, and when there are comments reading 1000 chars its going to cause bother..

[jaymc]

Any other ways apart from using PHP to stop it or by using another query?

[soycharliente]

What's your db structure? What info are you trying to get?

[jaymc]

id | user | poster | timestamp | comment | valid

 

From the query I need the last 5 comments (id | user | poster | timestamp | comment )

 

I also need the total number of comments in the table

 

So I may only be displaying 5 but I want to to show how many comments in total...

[Wildbug]

You could use SQL_CALC_FOUND_ROWS/FOUND_ROWS(); it's also two queries, but it should be better than doing the query twice.  It's intended for finding how many rows would have been returned in a query with a LIMIT clause.

[Wildbug]

In case you need an example:

 

<?php
$query = 'SELECT SQL_CALC_FOUND_ROWS * FROM comments ORDER BY timestamp DESC LIMIT 5';
$result = mysql_query($query);
if ($result && mysql_num_rows($result)) {
$total_rows = mysql_result(mysql_query('SELECT FOUND_ROWS()'),0);
while ($row = mysql_fetch_row($result)) {
	// print comments, etc.
}
} else {
// print error or something
}

?>

[soycharliente]

IF you're going to run two queries anyway, why not just keep it simple?

 

Wildbug, is yours faster/more efficent?

 

<?php
$total_rows = mysql_query("select count(id) as total from table");
$result = mysql_query("select * from tableorder by id desc limit 5");
?>

[Wildbug]

My benchmarks have both methods about the same on a table of 91 records (the largest table I happen to have on my test db...not so big).

 

In this case, the query is trivial.  SQL_CALC_FOUND_ROWS is more efficient in situations involving more complex queries and perhaps larger results sets.  Essentially the server is not required to run the query twice to satisfy a count and a query, so that's where you gain efficiency.  When I added a WHERE clause, the SCFR method gained an advantage.

 

I'd encourage jaymc to benchmark both versions of the actual query on the actual data to see which is better for that particular dataset.

[jaymc]

I will look into this

 

Thats another thing, the queries run so fast its hard to see which is faster

 

Is there a way to display query execution time

 

However, that will fluctuate depending on current server load

 

1 second there could be 50 queries hitting the server

 

The next only 5

 

Its going to be really hard to get an accurate result of which is faster?

[jaymc]

Edited

[Wildbug]

To benchmark these, you need to take multiple execution times to get a reliable time.... something like this:

 

<?php

$loops = 100;

$start = microtime(1);

for ($i = 0; $i < $loops; $i++) {
    // put test code #1 here
}

echo "First way took <b>", microtime(1) - $start, "</b> seconds.<br><br>\n";

$start = microtime(1);

for ($i = 0; $i < $loops; $i++) {
    // put test code #2 here
}

echo "Second way took <b>", microtime(1) - $start, "</b> seconds.<br><br>\n";

?>

 

With 100 loops on those queries we have been discussing, the execution times were around 0.08-0.09 seconds... which is no big deal.  But if your site gets a lot of traffic, maybe you'll need to make your code as efficient as possible.