|
|
Posted by Rik Wasmus on 01/18/08 19:51
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...
--
Rik Wasmus
Navigation:
[Reply to this message]
|