Posted by Norman Peelman on 01/18/08 19:26

Rik Wasmus wrote:
> On Fri, 18 Jan 2008 15:35:24 +0100, Gary L. Burnore
> <gburnore@databasix.com> 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.
>
> 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

[Back to original message]