On 5/9/07, James Tu <[EMAIL PROTECTED]> 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.
