How to incorporate SQL_CALC_FOUND_ROWS and FOUND_ROWS() into existing web application

Question

I just read about mysql's SQL_CALC_FOUND_ROWS and FOUND_ROWS() which helps you get the total result size of a previously executed query by dropping the LIMIT conditions. Now i want to use this feature in an existing web application.

I have a few approaches in mind, but I'm not sure which is best:

1) Does PHP already have functions that use the FOUND_ROWS() feature? I'm using php 5 with mysqli and mysql 5 with innodb storage engine.

2) The application I'm building already has it's own data access object that wraps the mysqli library. It is responsible for generating frequently used CRUD queries. Should I edit this object to include the SQL_CALC_FOUND_ROWS on all SELECT queries? What complications might exist?

Any other ideas/feedback?

Answer 1

1) Does PHP already have functions that use the FOUND_ROWS() feature? I'm using php 5 with mysqli and mysql 5 with innodb storage engine.

I thought mysql_num_rows does it, but after a little browsing I think I might be wrong.

UPDATE: Yes, I'm wrong :)

2) The application I'm building already has it's own data access object that wraps the mysqli library. It is responsible for generating commonly used CRUD queries. Should I edit this object to include the SQL_CALC_FOUND_ROWS on all SELECT queries? What complications might exist?

SQL_CALC_FOUND_ROWS will try to calculate number of rows that would be returned be there no LIMIT clause in your query.

This will utilize metadata in one and only one case:

1. When your table uses MyISAM engine, AND
2. When your query has no WHERE clause.

In other cases SQL_CALC_FOUND_ROWS will count the rows one by one, impacting performance.

Don't append it to each and every query.

Answer 2

1.) No, php doesn't have a function like this.

2.) I think it's better to use SQL_CALC_FOUND_ROWS only when you really need it.