I'm trying to make Cakephp paginate take advantage of the SQL_CALC_FOUND_ROWS feature in mysql to return a count of total rows while using LIMIT. Hopefully, this can eliminate the double query of paginateCount(), then paginate().

• http://dev.mysql.com/doc/refman/5.0/en/information-functions.html#function_found-rows

I've put this in my app_model.php, and it basically works, but it could be done better. Can someone help me figure out how to override paginate/paginateCount so it executes only 1 SQL stmt?

/**
 * Overridden paginateCount method, to using SQL_CALC_FOUND_ROWS
 */
public function paginateCount($conditions = null, $recursive = 0, $extra = array()) {
    $options = array_merge(compact('conditions', 'recursive'), $extra);
    $options['fields'] = "SQL_CALC_FOUND_ROWS `{$this->alias}`.*";

Q: how do you get the SAME limit value used in paginate()?

    $options['limit'] = 1;   // ideally, should return same rows as in paginate()     

Q: can we somehow get the query results to paginate directly, without the extra query?

    $cache_results_from_paginateCount = $this->find('all', $options);   
    /*
     * note: we must run the query to get the count, but it will be cached for multiple paginates, so add comment to query
     */
    $found_rows =  $this->query("/* do not cache for {$this->alias} */ SELECT FOUND_ROWS();");
    $count = array_shift($found_rows[0][0]);
    return $count;
}   

/**
 * Overridden paginate method
 */
public function paginate($conditions, $fields, $order, $limit, $page = 1, $recursive = null, $extra = array()) {
    $options = array_merge(compact('conditions', 'fields', 'order', 'limit', 'page', 'recursive'), $extra);

Q: can we somehow get $cache_results_for_paginate directly from paginateCount()?

    return $cache_results_from_paginateCount;  // paginate in only 1 SQL stmt
    return $this->find('all', $options);   // ideally, we could skip this call entirely
}