mysql - Zend paginator running very slow 1.12 -

mysql - Zend paginator running very slow 1.12 -

i using zend paginator fetching records , listing same.

there four joins , have indexed respective fields , getting required fields listing in tables. total number of records might 5 million approx. have tried optimize maximum , lastly found below code best.

$select = new zend_paginator_adapter_dbselect($select); $select->count($select); $paginator = new zend_paginator($select);

but this, takes more 40sec load page. can help me in getting improve performance.

i give little update question. below 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;

looking @ code found few things can improve below.

don't utilize "select * from", select fields need. view's much faster raw queries. explain query check execution time etc.

for example:

create view user_data_view select t1.column1,t1.column2,t1.column3 t2.column1,t2.column2,t3.column1 t3.column2,t4.column1,t5.column1 .... test1 t1 bring together test2 t2 t2.id = t1.test2_id left bring together test3 t3 t3.id = t1.test3_id bring together test4 t4 t4.id = t3.test3_id bring together test5 t5 t5.id = t5.test4_id

now utilize view retrieve data.

select column(s) user_data_view

something below retrieve info view, using limit clause best option.

$modelobject = new your_model_view; $select = $this->select(); $modelobject->fetchall($select);

hope helps!

cheers!

mysql zend-framework zend-db-table zend-db-select zend-paginator