Thanks David!

This the kind of answer that I was looking for (more about general
PHP and MySQL performance)
I think b/c of the way the tables are designed, I have to perform
multiple queries, unfortunately.

I think I'll have to do some performance testing at some point. But
for now I will let MySQL do the work instead of filtering with an IF
in PHP.

-James

On May 10, 2007, at 12:20 PM, David T. Ashley wrote:

> On 5/9/07, James Tu <jtu@esidesign.com> wrote: The database server
> and the web server are on separate machines.
> Table A contains a record for each user.
> Let's say Table B contains 'relationship' information. They can be
> of type 'friend' or 'family'.
> If a user knows another user, this relationship would be kept in this
> table, along with the type of relationship. Table B can get big.
> 10,000's or maybe 100,000's.
>
>
> I'm doing a query in PHP and want to end up with two arrays.
> One for type friend and one for type family.
>
> Which is better:
> (Method 1) Do ONE query for all the records that meet a certain
> criteria (let's say 'active'). Then use PHP to loop through the
> results and put each record into either the friend array or the
> family array.
>
> (Method 2) Do TWO queries. One just for friend. Loop through the
> records and put into friend array;
> Then do another query for family...and loop through again.
>
> In general, you don't want to introduce arbitrarily large result
> sets into PHP. PHP is fast, but there are memory limits and speed
> of iteration limits.
>
> In general, you want to structure things so that MySQL returns
> exactly the results you need, and in the order you need.
>
> In general:
>
> a)Check your database design to be sure that the queries you are
> interested in are O(log N). If not, make them that way, by
> rethinking your database design and/or adding indexes.
>
> b)See if you can get all the data you want in one query. In the
> example you gave, I think the WHERE clause syntax will allow
> checking for certain of an enumerated type, i.e WHERE (X=3 OR
> X=5) ... that kind of thing. So, retrieving friends and family in
> one query shouldn't be a problem. Two queries should not be required.
>
> Here is what you need to remember:
>
> a)Designs that aren't O(log N) for the queries you are interested
> in often catch up with you as the database grows.
>
> b)There is a speed hierarchy involved. PHP is the slowest of all,
> so if you loop over records in PHP it needs to be a guaranteed
> small set. MySQL takes a one-time hit parsing the SQL statement,
> but after that it can operate on the database FAR faster than PHP
> can. In general, let MySQL do the work, because it can do the
> sorting, filtering, etc. FAR faster than PHP.
>
> Dave.
>
>