SQL_CALC_FOUND_ROWS or COUNT(*) ???

[eluser]Atharva[/eluser]
Hello all,

This is the question for which I have been looking for an answer. In my earlier programming days, I was using COUNT() as a separate query for the pagination. Then one fine day, I discovered the term SQL_CALC_FOUND_ROWS , which pre-calculates the total records which can be retrieved by using FOUND_ROWS() function in a separate query. Mysql documentation here claims that it is much faster to use SQL_CALC_FOUND_ROWS with FOUND_ROWS() than using a separate count(*) query.

But in one of our project which involves millions of records, we found that the query takes too much time with SQL_CALC_FOUND_ROWS as it pre-calculates the total records. We switched to COUNT(*) and found that the performance was much better than the query with SQL_CALC_FOUND_ROWS. I also found out one blog here where this topic has been debated and there are some mix reaction and it seems that there is 50-50 between both methods. Some are saying that it is better to use SQL_CALC_FOUND_ROWS for most complex queries than COUNT() , and some are saying that use COUNT() for the db which contains huge amount of records while SQL_CALC_FOUND_ROWS can be used for smaller db.

I would like to know your views on this, what do you prefer to use. Can we have a debate on this?

[eluser]jedd[/eluser]
Apparently not

You say 'too much time', but don't say if this is an additional 3ms over the alternative, or a half a day of database pondering.

You mention COUNT(*). I recall reading something to the effect that COUNT(*) should be avoided, and COUNT(id) (or your PK, or at least an INDEX'd column) be used instead.

Perhaps you could try that first to see if there's any substantive performance benefits.

My gut feel is that COUNT(id) should be sufficiently fast - the DB really should be able to report that back very quickly indeed. Mixing and matching, or trying to predict if alternative functions can pre-calculate (I'm not sure what that means in this context) the size of a table faster, just sounds like too much work.

What other steps have you taken to improve the performance of your database? I reckon that'd be an easier vector to approach the problem on.

EDIT: Oh, meant to also add -- if you're seeing woeful performance when you try to paginate 'millions of rows', then the limiting factor might not be the counting part of that process.

[eluser]SneakyDave[/eluser]
FYI, I couldn't get the FOUND_ROWS to work correctly, as the FOUND_ROWS() is built as an element of the result object, and accessing it resulted in parse errors, so I used this....

After your query is performed, i.e.

Code:

$query = $this->db->get();

I do this to get the found rows...

Code:

$totalquery = $this->db->query('SELECT FOUND_ROWS() as total;');
$row= $totalquery->row();
echo $row->total;