Which would be faster?

PHP_Novice2

1.) Querying + grouping together ~50,000 rows in real-time, all the time

2.) Querying + grouping together ~50,000 rows in real-time once, then caching the output in a DB column

3.) Querying + grouping together ~50,000 rows in real-time once, then caching the output in a text file

Obviously, 1 will be the slowest, but will the performance increases of 2 or 3 justify the work needed to build a caching system? And which of the final 2 would be optimal?

harmor

Just try each and wrap them with "microtime()"
http://www.developertutorials.com/tutorials/php/php-script-timer-051013/page1.html

Another suggestion
You should paginate the 50,000 rows.

Piranha

Another way to go is to generate a html-page with all the information as it should look. Then the server don't have to do anything with php, and it will improve the speed. The problem is of course to generate the file, and I'm sorry but I haven't done it myself and can't help you with that.

Shrike

Later versions of MySQL have query caching which can speed things up a bit, but fetching 50,000 rows will be slow and memory intensive whatever the case.

I can't imagine that you actually need to fetch that many rows for a web page, so assuming it's for some other purpose a text file might be the best approach. Maybe you can elaborate what it is your doing.

PHP_Novice2

My visitors can generate a statisical report by entering parameters into a form. One such option is to specify a date range for the data, and if they choose statistics from the earliest+latest possible dates, the resulting query can take awhile.

The query creates 2 temporary tables and then joins them. The source tables are 50,000 and 3,000 records, respectively.

jdorsch

PHP Novice wrote:
My visitors can generate a statisical report by entering parameters into a form. One such option is to specify a date range for the data, and if they choose statistics from the earliest+latest possible dates, the resulting query can take awhile.

The query creates 2 temporary tables and then joins them. The source tables are 50,000 and 3,000 records, respectively.

Are you fetching all the records and then doing stats on them within PHP? If so you should be doing all your stats within SQL statements and not have to select all the records. Just make sure you have proper indexes for whatever you are doing.