如何在Zend中优化我的查询？

Question

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 filtered 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

Answer 1

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.