Php – Getting total number of rows using SQL_CALC_FOUND_ROWS in Zend DB Table Select

MySQLPHPsqlzend-framework

Is there a way to get the total number of rows in Zend db select like with using SQL_CALC_FOUND_ROWS in a regular mysql query. I haven't been able to find a similar functionality for this apart from running the same query without the limit clause.

Best Answer

$db->select()
   ->from($tableName, array(
       new Zend_Db_Expr('SQL_CALC_FOUND_ROWS id'), 
       'name', 
       'price'
   ));

You could also try replacing all cols with COUNT(*) and running the query second time. It may actually be more efficient (even if it's counter-intuitive). This was the case for my app.

You can do it like this:

$select->reset('cols')->reset('limit')->cols('COUNT(*)'); //there is a constant for the 'cols' in Select class
$db->query($select);

Related Solutions

Mysql – Which is fastest? SELECT SQL_CALC_FOUND_ROWS FROM `table`, or SELECT COUNT(*)

It depends. See the MySQL Performance Blog post on this subject: To SQL_CALC_FOUND_ROWS or not to SQL_CALC_FOUND_ROWS?

Just a quick summary: Peter says that it depends on your indexes and other factors. Many of the comments to the post seem to say that SQL_CALC_FOUND_ROWS is almost always slower - sometimes up to 10x slower - than running two queries.

Mysql – Get record counts for all tables in MySQL database

SELECT SUM(TABLE_ROWS) 
     FROM INFORMATION_SCHEMA.TABLES 
     WHERE TABLE_SCHEMA = '{your_db}';

Note from the docs though: For InnoDB tables, the row count is only a rough estimate used in SQL optimization. You'll need to use COUNT(*) for exact counts (which is more expensive).

Best Answer

Related Solutions

Mysql – Which is fastest? SELECT SQL_CALC_FOUND_ROWS FROM `table`, or SELECT COUNT(*)

Mysql – Get record counts for all tables in MySQL database

Related Topic