Re: better practice - php code or multiple mysql queries?

Post removed (X-No-Archive: yes)

Re: better practice - php code or multiple mysql queries?

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
> 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've done this.

Itseasierto do one big query, sort by whatever, iterate through the
array and write a new header everytime the name changes

e.g. select surname,... from mytable, order by name gets the lot in teh
write order, and then a simple loop with something like posting a
varablee for the current first letter, and if it DIOES'T match the
initial of the name you are about to print, executing a bit of code that
prints a new header, aand set it to the current first name.

PHP may be slow, but not as slow as the overhead on an SQL call with the
attendant file system shuffling.

Re: better practice - php code or multiple mysql queries?

On Fri, 18 Jan 2008 15:35:24 +0100, 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
> 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.

With a limited amount of users/entries in the database, you're going to
display them all anyway, So query the lot. A simple count query could tell
you which one you are going to use, a SELECT DISTINCT
SUBSTRING(fieldname,1,1) FROM tablename; (or group by) could tell you all
starting letters. Then depending on what you want query all entries, or
just by starting letter. Seems like 3 queries either case to me.
--
Rik Wasmus

Re: better practice - php code or multiple mysql queries?

Rik Wasmus wrote:
> On Fri, 18 Jan 2008 15:35:24 +0100, 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
>> 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.
>
> With a limited amount of users/entries in the database, you're going to
> display them all anyway, So query the lot. A simple count query could
> tell you which one you are going to use, a SELECT DISTINCT
> SUBSTRING(fieldname,1,1) FROM tablename; (or group by) could tell you
> all starting letters. Then depending on what you want query all entries,
> or just by starting letter. Seems like 3 queries either case to me.
> --Rik Wasmus

Setting the mysql_query_cache would also speed things up since you're
calling the whole lot. Even after additions the result set will be
cached after the first call (SELECT) and MySQL won't need to go to disk.



--
Norman
Registered Linux user #461062

Re: better practice - php code or multiple mysql queries?

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

Re: better practice - php code or multiple mysql queries?

On Fri, 18 Jan 2008 20:46:01 +0100, Michael Austin
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
>> 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

Re: better practice - php code or multiple mysql queries?

Post removed (X-No-Archive: yes)

Re: better practice - php code or multiple mysql queries?

"The Natural Philosopher" wrote in message
news:1200672543.35803.0@iris.uk.clara.net...

> I've done this.
>
> Itseasierto do one big query, sort by whatever, iterate through the array
> and write a new header everytime the name changes
>
> e.g. select surname,... from mytable, order by name gets the lot in teh
> write order, and then a simple loop with something like posting a varablee
> for the current first letter, and if it DIOES'T match the initial of the
> name you are about to print, executing a bit of code that prints a new
> header, aand set it to the current first name.
>
> PHP may be slow, but not as slow as the overhead on an SQL call with the
> attendant file system shuffling.

phil...i've never, ever seen *any* scripting language that can out-perform a
database. sorry, i don't buy that. that one (whatever is meant by that)sql
query can run cached and be optimized by almost any db. whatever system
shuffling is to be done, it certainly will be mechanically LESS than hitting
the db n times to get the same result...especially considering that php is
involved between calls rather than just one process.

make a believer of me, though, and i will happily change my mind. :)

Re: better practice - php code or multiple mysql queries?

"Gary L. Burnore" wrote in message
news:fmr27l$666$4@blackhelicopter.databasix.com...
> On Fri, 18 Jan 2008 20:51:38 +0100, "Rik Wasmus"
> wrote:
>
>>On Fri, 18 Jan 2008 20:46:01 +0100, Michael Austin
>> 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
>>>> 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? :^)

Re: better practice - php code or multiple mysql queries?

Steve wrote:
> "Gary L. Burnore" wrote in message
> news:fmr27l$666$4@blackhelicopter.databasix.com...
>> On Fri, 18 Jan 2008 20:51:38 +0100, "Rik Wasmus"
>> wrote:
>>
>>> On Fri, 18 Jan 2008 20:46:01 +0100, Michael Austin
>>> 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
>>>>> 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)

Re: better practice - php code or multiple mysql queries?

Post removed (X-No-Archive: yes)

Re: better practice - php code or multiple mysql queries?

"Gary L. Burnore" wrote in message
news:fmr8f4$gqf$1@blackhelicopter.databasix.com...
> On Fri, 18 Jan 2008 15:45:52 -0600, Michael Austin
> wrote:
>
>>Steve wrote:
>>> "Gary L. Burnore" wrote in message
>>> news:fmr27l$666$4@blackhelicopter.databasix.com...
>>>> On Fri, 18 Jan 2008 20:51:38 +0100, "Rik Wasmus"
>>>> wrote:
>>>>
>>>>> On Fri, 18 Jan 2008 20:46:01 +0100, Michael Austin
>>>>> 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
>>>>>>> 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.

exactly. :)

Re: better practice - php code or multiple mysql queries?

Steve wrote:
> "The Natural Philosopher" wrote in message
> news:1200672543.35803.0@iris.uk.clara.net...
>
>> I've done this.
>>
>> Itseasierto do one big query, sort by whatever, iterate through the array
>> and write a new header everytime the name changes
>>
>> e.g. select surname,... from mytable, order by name gets the lot in teh
>> write order, and then a simple loop with something like posting a varablee
>> for the current first letter, and if it DIOES'T match the initial of the
>> name you are about to print, executing a bit of code that prints a new
>> header, aand set it to the current first name.
>>
>> PHP may be slow, but not as slow as the overhead on an SQL call with the
>> attendant file system shuffling.
>
> phil...i've never, ever seen *any* scripting language that can out-perform a
> database. sorry, i don't buy that.

I think you miss the point.

Consiuder
with selects * from ALL_NHS_PATIENTS

for eaxmple. Bet you teh first is quicker.

So that mens your statement in teh limit is false. Some database queries
can be slower than some PHP statements.


Seciondly, which is quicker?

itersiaing through an array 27 times, on code thats already interpreted,
or pulling that array, possibly off disk, 27 times.

For a start, the php overhead to set up the call to mysql is probably as
bad as the php code to run through the array.

Secondly, what do you think MySQL IS, if not an interpreted language?

So in all case we are running a load of interepted code.

The only difference is that in the databsae area, there are two
interpreters involved, connected by a socket, plus potentally loads of
disk access, or at least RAM cached disk access.

Its patently OBVIOUS that the php will be faster.




that one (whatever is meant by that)sql
> query can run cached and be optimized by almost any db. whatever system
> shuffling is to be done, it certainly will be mechanically LESS than hitting
> the db n times to get the same result...especially considering that php is
> involved between calls rather than just one process.
>

Well that is what I said.
> make a believer of me, though, and i will happily change my mind. :)
>

I think you need to make it up first.


The choice is between loading the same data 27 times, and having simple
PHP code, or having the data loaded once, and using more complex php
code to run through it 27 times.

If the data is in anyway large, its vastly more efficient to load it
into PHP once, and then play games with it.


>

Re: better practice - php code or multiple mysql queries?

> 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

Ah, that would be my code. I never put more fields in a table than I'm
interested in. So SELECT * gives me exactly all fields I'm interested in...

Regards,
--
Willem Bogaerts

Application smith
Kratz B.V.
http://www.kratz.nl/

Re: better practice - php code or multiple mysql queries?

Willem Bogaerts wrote:
>> 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
>
> Ah, that would be my code. I never put more fields in a table than I'm
> interested in. So SELECT * gives me exactly all fields I'm interested in...
>
> Regards,
And you want to know about all of them all the time?

Or are all the tables simply two field tables with links to other tables
so every select is a multi table join?

Re: better practice - php code or multiple mysql queries?

Willem Bogaerts wrote:
>> 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
>
> Ah, that would be my code. I never put more fields in a table than I'm
> interested in. So SELECT * gives me exactly all fields I'm interested in...
>
> Regards,

It's still not a good idea to use SELECT *. What happens if the next
programmer comes along and adds a 10Mb BLOB column to the table and
stuffs 10MB images in it?

--
==================
Remove the "x" from my email address
Jerry Stuckle
JDS Computer Training Corp.
jstucklex@attglobal.net
==================

Re: better practice - php code or multiple mysql queries?

Jerry Stuckle wrote:
> Willem Bogaerts wrote:
>>> 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
>>
>> Ah, that would be my code. I never put more fields in a table than I'm
>> interested in. So SELECT * gives me exactly all fields I'm interested
>> in...
>>
>> Regards,
>
> It's still not a good idea to use SELECT *. What happens if the next
> programmer comes along and adds a 10Mb BLOB column to the table and
> stuffs 10MB images in it?
>
One had better hope he also adjusts the myqsql calls ;-)

Re: better practice - php code or multiple mysql queries?

The Natural Philosopher wrote:
> Jerry Stuckle wrote:
>> Willem Bogaerts wrote:
>>>> 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
>>>
>>> Ah, that would be my code. I never put more fields in a table than I'm
>>> interested in. So SELECT * gives me exactly all fields I'm interested
>>> in...
>>>
>>> Regards,
>>
>> It's still not a good idea to use SELECT *. What happens if the next
>> programmer comes along and adds a 10Mb BLOB column to the table and
>> stuffs 10MB images in it?
>>
> One had better hope he also adjusts the myqsql calls ;-)
>

If the code is properly written, the next programmer only has to adjust
the calls which need to return the new field.

SELECT * is just sloppy programming.

--
==================
Remove the "x" from my email address
Jerry Stuckle
JDS Computer Training Corp.
jstucklex@attglobal.net
==================