How to optimize my query in 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 a previous zend request, I get patient records and insurance based on some conditions in the where section. I have to get (1) The total number of records, (2) The total filtered records, as well as (3) Patient reports for display on the web page.

The problem is that in my previous query I should get records 3 times, which slows down performance when there are 10,000 records. How can I optimize my query so that it retrieves records only once, or should there be a separate query for counting that will receive only the total number of records, and not a selection of all records.

Each answer will be appreciated.

Thanks Thanks

Answer 1

Something like this should start, unfortunately, I have no way to check it now.

 // 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 reuse the same Zend_Db_Select object by overwriting $select->from() to remove the COUNT(*) add-on.