How to optimize my query in Zend?

This is my simple query in mysql/zend:

// Get Patients
$table = new Model_Patient_DbTable();
$select = $table->select();
$select->from( 'patient' );
$select->setIntegrityCheck( false );

// insurance join                                                               
$select->joinLeft( 'insurance', 'patient.insuranceId=insurance.Id', 
                                 array( 'insName' => 'insName'));

// Get total no of records
$totalRecords = count( $table->fetchAll( $select ) );

// Filters 
if( $inactive ) {
   $select->where('patient.inactive = ?', $inactive );
}
// Other where clause conditions

// Fetch filtered patient records        
$patientRecords = $table->fetchAll( $select );

// Get total no of filtered patient records 
$filteredRecords = count( $table->fetchAll( $select ) );

In above zend query I am get getting patient records and their insurances based on some conditions in where clause. I have to get (1) Total No. of Records, (2) Total No. of f开发者_开发百科iltered Records and also (3) Patient Records to show on webpage.

Problem is that in my above query I have to fetch records 3 times which slow the performance when there are 10,000 records. How can I optimize my query that it fetch the the records only once OR there should be a separate query for counting that will only get total No of records instead of fetching all records.

Every reply will be appreciated.

Thanks Thanks

Something like this should get you started, unfortunately I don't have a way of testing this currently.

// Get Patients
$table = new Model_Patient_DbTable();

// Get Total records
$select = $table->select();
$select->from($table, array('COUNT(*) as row_count'));
$select->setIntegrityCheck(false);
$select->joinLeft('insurance', 'patient.insuranceId = insurance.Id', array('insName' => 'insName'));
$result = $table->fetchAll($select);
$totalRecords = $result[0]->row_count;

// Filters
if ($inactive) {
    $select->where('patient.inactive = ?', $inactive);
}

// Get Total filtered records
$result = $table->fetchAll($select);
$filteredRecords = $result[0]->row_count;

// Get filtered records
$select = $table->select();
$select->from($table);
$select->setIntegrityCheck(false);
$select->joinLeft('insurance', 'patient.insuranceId = insurance.Id', array('insName' => 'insName'));
if ($inactive) {
    $select->where('patient.inactive = ?', $inactive);
}
$patientRecords = $table->fetchAll($select);

Note: You may be able to re-use the same Zend_Db_Select object by overwriting the $select->from() to remove the COUNT(*) addition.