Zend paginator running very slow 1.12

Question

I am using zend paginator for fetching records and listing the same.

There are four joins and I have indexed all the respective fields and I am getting only the required fields for listing in all the tables. Total number of records might be 5 million approx. I have tried to optimize the maximum and last I found this below code is best.

$select = new Zend_Paginator_Adapter_DbSelect($select);
$select->count($select);
$paginator = new Zend_Paginator($select);

But even with this, it takes more than 40sec to load the page. Can someone help me in getting better performance.

I will give little update to my question. below is my code

$query = $this->select();
$query->setIntegrityCheck(false);

$query->from(array('test1' => '*')))
->join(array('test2' => 'test2'), 'test2.id = test1.test2_id', array('*'))
->joinLeft(array('test3' => 'test3'), ("test1.test2_id = test3.test2_id'"), array('*')
->joinLeft(array('test4' => 'test4'), "test1.test4_id = test4.test4_id", array('*'))
->join(array('test5' => 'test5'), 'test1.test5_id = test5.id', array('*'));

$paginator = new Zend_Paginator(new Zend_Paginator_Adapter_DbTableSelect($query));

$paginator->setItemCountPerPage(ITEM_PER_PAGE);
$paginator->setCurrentPageNumber($this->getRequest()->getParam('page', 1));
$paginator->setPageRange(PAGE_RANGE);

$this->view->paginator = $paginator;

Execution plan would be nice (use EXPLAIN before query). Then check what takes so long - paginated query or count query. BTW --> count does not take parameters and is not needed. — Volvox, Nov 03 '14 at 09:25
Count query takes **24sec** to execute. FYI, MySQL **Sending data** takes total of 30sec for fetching 2.5 million records. — Prabhu Khanna Mahadevan, Nov 04 '14 at 05:29

score 0 · Answer 1 · answered Nov 06 '14 at 22:41

Looking at your code I found few things you can improve as below.

Don't use "SELECT * FROM", select only the fields you need.
View's are much faster than the raw queries.
Explain query to check execution time etc.

For example:

CREATE VIEW USER_DATA_VIEW AS
SELECT 
T1.column1,T1.column2,T1.column3
T2.column1,T2.column2,T3.column1
T3.column2,T4.column1,T5.column1 
....
FROM test1 as T1
JOIN test2 as T2 T2.id = T1.test2_id
LEFT JOIN test3 as T3 T3.id = T1.test3_id
JOIN test4 as T4 T4.id = T3.test3_id
JOIN test5 as T5 T5.id = T5.test4_id

Now use VIEW to retrieve data.

SELECT column(s) FROM USER_DATA_VIEW

Something like below to retrieve data from VIEW, using LIMIT clause also another best option.

$modelObject = new Your_Model_View;

$select = $this->select();
$modelObject->fetchAll($select);

Hope this helps!

Cheers!

Zend paginator running very slow 1.12

1 Answers1