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 <[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.
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
