Jed Verity wrote:

>In general, is it faster and more efficient to query a MySQL database once with
>a large SQL select statement and then loop through the huge resulting table?
>Or does it make better sense to perform a number of smaller queries with
>smaller resulting tables?
This is a hard question to give an answer to. It depends largely on the 
amount of data you're talking about, the amount of memory on the machine 
your code is executing from, whether you are using persistent database 
connections, etc.

In general, if you use persistent database connections, each additional 
query after the first will be quite fast. I'm sure your question stems 
from seeing someone say that queries are the most common bottleneck for 
Web applications, and this is true to a point.

If the one query you speak of is huge, try some tests and see how much 
memory PHP has to use to maintain that in memory. Consider that multiple 
requests during the time that PHP is "holding" that data in memory will 
result in a pretty decent chunk of memory being used. If you have 
endless amounts of memory (some huge unix machine with 20+ GB of RAM), I 
would say go for the one query method. It is definitely going to be 
faster, so long as you have enough memory to handle it. In most cases, 
if your approach ends up running your machine out of memory under heavy 
load, it would be a poor approach, because it would end up slowing 
*everything* down.

So, maybe that gives you some things to think about. Maybe someone else 
can come up with a more conclusive answer.

In summation:
1) using memory to keep *all* database results can improve performance 
if you have plenty of memory to handle this approach
2) if not, try very specific queries that retrieve just the data you 
need, and carefully free all results as you finish - also try to use 
persistent connections if you forsee your site being under heavy load.

I personally take approach #2, because most of my environments have had 
sane (less than 5 GB) amounts of memory. I *think* this method would be 
more appropriate for most people, even though it seems like you end up 
hitting the database a lot. I've generally gotten extraordinary 
performance (page execution takes less than a tentth of a second 
searching through a database with 50,000 records) from a multi-homed Web 
host with PHP/MySQL, even with 30,000+ transactions/day. It's also more 
difficult to get specific data sets with one query; that approach 
usually requires that you have to take a greatest common denominator 
style approach and end up with way more data than you really need.

I'd be curious to see some other opinions. There's mine. :)


PHP General Mailing List (
To unsubscribe, visit:

Reply via email to