Posted by Michael Austin on 01/18/08 19:46

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...

Be careful what you ask for - you might just get it... :)

Michael Austin.

[Back to original message]