|
|
Posted by Gary L. Burnore on 01/18/08 22:16
On Fri, 18 Jan 2008 15:45:52 -0600, Michael Austin
<maustin@firstdbasource.com> wrote:
>Steve wrote:
>> "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? :^)
>>
>>
>Sorry, but an AS400 is a LONG way from being a mainframe... (ie 360/390)
Maybe he meant it only takes an as400 to bog a mainframe? Heh.
--
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
===========================================================================
Navigation:
[Reply to this message]
|