Posted by Gary L. Burnore on 01/18/08 20:29

On Fri, 18 Jan 2008 20:51:38 +0100, "Rik Wasmus"
<luiheidsgoeroe@hotmail.com> wrote:

>On Fri, 18 Jan 2008 20:46:01 +0100, Michael Austin
><maustin@firstdbasource.com> wrote:
>
>> Gary L. Burnore wrote:
>>> [Carolyn's post left intact and comp.lang.php added]
>>> On Fri, 18 Jan 2008 09:27:47 -0500, Carolyn Marenger
>>> <cajunk@marenger.com> wrote:
>>>
>>>> I am new to PHP/MySQL. I would like some of your thoughts on when to
>>>> use php code and when to use mysql queries.
>>>>
>>>> In the case I am working on, a learning project for myself, I am
>>>> writing a contact management application. Basically a phone book
>>>> with a many to many relationship between individuals and
>>>> organizations, and one to many between individuals/organizations and
>>>> the relevant data such as phone numbers.
>>>>
>>>> I am working on a page which lists all the individuals. I have a
>>>> horizontal listing at the top containing all the unique surname
>>>> initials, which link to headings as the list progresses. ie:
>>>>
>>>> -----
>>>> A B N
>>>>
>>>> A
>>>> Abnew, Georgina
>>>> Adwit, Dave
>>>>
>>>> B
>>>> Biltmore, Garth
>>>>
>>>> N
>>>> Niles, Paulina
>>>> Norton, Alicia
>>>> -----
>>>>
>>>> Currently, I have MySQL select all the unique initials and process
>>>> that into an array. The array is dumped to the screen as the index at
>>>> the top. Next the array is used for MySQL selects of surnames with
>>>> that initial.
>>>>
>>>> In the above example that would result in 6 MySQL queries. Assuming
>>>> only English based names, no foreign character sets, no numbers, no
>>>> symbols, that would result in a maximum of 27 queries.
>>>>
>>>> I could rewrite the PHP code to select all the names in a single query
>>>> . Then use PHP to process the list to make the index, headings, and
>>>> list the names.
>>>>
>>>> For a small private phone book, either way, so what - big deal. In a
>>>> public database, there could conceivably be a large amount of network
>>>> overhead processing the Mysql queries. However, from the reading I
>>>> have been doing, MySQL is very fast, PHP being interpreted, may end up
>>>> being slower.
>>>>
>>>> So, anyone care to enlighten me? I would love your thoughts,
>>>> comments, experiences.
>>>>
>>> I've added the crosspost because I believe this is an extremely good
>>> question that doesn't get the attention it deserves. You should get a
>>> lot of opinions on this.
>>> Here's mine:
>>> One query to sql is better than 6 and it's certainly better than 27.
>>> PHP could process the list quite quickly. If your DB is on a
>>> different server than your web site (good practice to keep your DB
>>> behind a firewall>, then you're crossing your network up to 27 times
>>> instead of just once.
>>
>> I would agree that this may work in this one situation, however, when
>> those datasets get too large - with large numbers of request for that
>> data, you can do more harm than good.
>>
>> I recently fixed some code that was causing over 400MB per request to be
>> returned for processing - and literally crippled an IBM mainframe. to
>> the point that once we shut off the web app - it took the frame more
>> than 7 hours to catch up on it's batch processing. One of those - let's
>> let the application process the data. Again, in a very small number of
>> situations this **may** be okay, in others, it can be crippling.
>>
>> Things to remember:
>> 1) KNOW YOUR DATA - what it is and how much you have.
>> 2) KNOW YOUR "customer" - how many, how they might use the data.
>> 3) Your "customer" will ALWAYS use your application in ways you never
>> dreamed of...
>
>4) Never ask for more data then you're really sure you're going to use.
>The numerous times I've seen 'SELECT *' in production code...

Right. As I read her example, she planned on using the data, but
parsing it out up to 27 ways. (& btw, we all know how little it
takes to bog an IBM mainframe) :)
--
gburnore at DataBasix dot Com
---------------------------------------------------------------------------
How you look depends on where you go.
---------------------------------------------------------------------------
Gary L. Burnore | ÝÛ³ºÝ³Þ³ºÝ³³Ýۺݳ޳ºÝ³Ý³Þ³ºÝ³ÝÝÛ³
| ÝÛ³ºÝ³Þ³ºÝ³³Ýۺݳ޳ºÝ³Ý³Þ³ºÝ³ÝÝÛ³
Official .sig, Accept no substitutes. | ÝÛ³ºÝ³Þ³ºÝ³³Ýۺݳ޳ºÝ³Ý³Þ³ºÝ³ÝÝÛ³
| ÝÛ 0 1 7 2 3 / Ý³Þ 3 7 4 9 3 0 Û³
Black Helicopter Repair Services, Ltd.| Official Proof of Purchase
===========================================================================

[Back to original message]