When programming in PHP, which method is faster?

1. a huge SQL statement with 24 UNIONs with few results;

or 2. a short SQL statement with thousands of results and a PHP loop that goes through the results to analyze them.

Depends on the data, but if you're indexed properly, it's usually faster to do it on the DB side. Doing aggregate functions over temporary tables that only have thousands of results shouldn't be too hard on the db at all.

Try your query on the command line (assuming your db reports the processing time) and if it's unacceptably long, try using the EXPLAIN syntax (available in some format on most dbs).

If you're not sure how to properly index the db for the query, post the query, related table structure ("EXPLAIN table_name"), current indexes ("SHOW INDEX IN table_name") and the results of "EXPLAIN [query]" I'll help you understand the results and set table indexes.

The results of your tests could vary quite a bit too if mysql keeps query results cached which it often does.

Generally though I'd expect the database being more efficient and faster.

Thank you both and thank you mattle for offering to explain the results. I may get back to you on this.

You shouldn't be guessing with these things. Use EXPLAIN to see if indexes are used and how many rows are scanned. And then benchmark the execution speed, guessing really doesn't give you anything.

The results of your tests could vary quite a bit too if mysql keeps query results cached which it often does.

Generally though I'd expect the database being more efficient and faster.

This is a good point, and it can screw with your testing. To evaluate query time on MySQL, use:

SET SESSION query_cache_type = OFF;

In a real world situation where you have the cache enabled it most likely will be enabled, though. So while that is good for testing the actual performance of the query it's not overly relevant for a real-world load scenario.

I've been a DB developer and admin for years, and have never seen a necessary use of that many unions...what, pray tell are you trying to do?

Number 2 for sure...but it really depends on the application...

Number 2 for sure...but it really depends on the application...

Care to elaborate? You seem to disagree with other posters.

I'd prefer splitting this huge SQL query into multiple queries which will both allow better caching on MySQL side and will ease debug when things go wrong.
In either cases, you should cache the precious result set you got from the DB.

Care to elaborate? You seem to disagree with other posters.

Suppose the query is to find the n rows with the least value for some expression.

Processing all the rows with a program will take whatever time it takes to read all the rows.

Obtaining the result with SQL will requiring reading all the rows and sorting them.

So which is quicker will depend on the speed of transferring the data to the program compared with the speed of sorting the rows.

On a system with limited memory and with the application and database server on the same hardware, it's likely that obtaining the results by a program wil be faster.

But if the application and database server are on separate hardware systems connected by say a 100mbps link, and the database server has plenty of RAM for sorting, then it's likely the SQL method will be faster.

(But if the database server software is clever at recognising LIMIT in conjunction with ORDER BY, and only sifts out the rows it needs instead of sorting the entire result set, it may be quicker in both cases.)

yo if its that complex then u probably want to build cache files anyway. data retrieval is going to cause load and delay.

It depends a lot , but method 1 is more useful.