minimizing no of query in Zend

Question

Hi,

first thing I want to know in between two algorithms which one is better

select table1.* from tabel1 inner join table2 on table1.id = table2.table1_id;

and then to extract or

select * from table2;

and then using foreach loop

select * from table1 where table1.id = table2.table1_id

*Please tell me a plausible reason also

and now I am using zend and i believe that first one is faster and better than second (i don't know why ...just some preconception)

and in zend db profiler, every time I am getting query :DESCRIBE menu_items time: 0.00084590911865234

is there a way to minimize it?

and please tell me how to join two tables in zend using zend components

regards,

Answer 1

Using inner join is faster than php loop due to the query response time. In the first one you'll do just one query and the in the second many. The database is prepared to retrieve data, this means that is much faster then join tables manually whith php through different queries.

To join with zend, you need this (Assuming you're on Zend_Db_Table):

$select = $this->select()->setIntegrityCheck(false);
$select->from(array('t1'=>'table1'))
       ->join(array('t2'=>'table2'),'t2.table1_id =t1.id','*')
       ->where('t1.deleted =?',0) 
       ->group('t1.id')
       ->order('t1.date DESC')
       ->limit(4);
$result = $this->fetchAll($select);

To prevent DESCRIBE queries you can hardcode the table structure or cache it. Check here:

http://framework.zend.com/manual/en/performance.database.html