|
|
Posted by Steve on 01/18/08 21:00
"Gary L. Burnore" <gburnore@databasix.com> wrote in message
news:fmr27l$666$4@blackhelicopter.databasix.com...
> 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) :)
can you say, as400? :^)
Navigation:
[Reply to this message]
|