Page 1 of 1

SQL or PHP

Posted: Tue Dec 11, 2007 7:09 pm
by shiznatix
Ok yes, I am using Zend Framework. So my question...

if I use the Zend DB Table class I can build select queries like so:

Code: Select all

$select = $this->THINGY->getAdapter()->select()
->from(
    'table',
    array(
        'one_single_row',
    )
);
and then I execute the query and am on my way. Now, here is where my question really is. I could do a simple thing thing like this:

Code: Select all

$select = $this->THINGY->fetchAll();
which gets the same results that I need. The problem is that the table may have 30 rows and I only need one column while the first example (using more php than sql) gets it done one way and the second example (using sql more than php) also gets it done (but with 29 extra rows returned).

So what I am wondering is if the extra "mile" to use the Zend DB class to make my select statement is faster than just selecting everything and just using what I need in the PHP code.

I suppose my question really is this: Is going the extra mile using PHP to make my queries as optimal as possible worth it or should I just use MySQL to get everything from maybe a 30 row column and use only 1 of the rows returned in my script? Which is more optimal?

If I am not being clear let me know so I can give more details.

Posted: Tue Dec 11, 2007 7:23 pm
by Benjamin
What's more important to you and this application. The time it takes to create efficient queries or the time lost doing it?

Posted: Wed Dec 12, 2007 3:59 am
by shiznatix
the end desire is a page that loads as fast as possible. That is the real end goal.

Posted: Wed Dec 12, 2007 4:27 am
by fredrik
My rule of thumb is to never pull more data then I need from the database. Let's make a comparsion:

Pulling *exactly* the data you need results in:
a) Less data to transmit over the link between the db/php
b) Less work for the database(if the query isn't extremely complicated)
c) Less work for php

Pullin *all* data that and then use PHP to filter it results in:
a) More data to transmit over the db/php link
b) More work for the database in terms of memory allocation
c) More work for php


I think the above three points speakfor it self.

Posted: Wed Dec 12, 2007 5:32 am
by shiznatix
interesting first post there fredrik. I was leaning that way but I wanted to make sure. Thanks