MySQL Lists are EOL. Please join:

List:General Discussion« Previous MessageNext Message »
From:David T. Ashley Date:May 10 2007 4:20pm
Subject:Re: Which is a better design?
View as plain text  
On 5/9/07, James Tu <jtu@stripped> 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.

Thread
Which is a better design?James Tu9 May
  • Re: Which is a better design?John Meyer10 May
  • Re: Which is a better design?David T. Ashley10 May
    • Re: Which is a better design?James Tu10 May
      • Re: Which is a better design?David T. Ashley10 May
        • Re: Which is a better design?James Tu10 May
          • Re: Which is a better design?David T. Ashley10 May
            • Re: Which is a better design?Michael Dykman10 May