Which one is less costly in terms of resources?

Question

Im on an optimization crusade for one of my sites, trying to cut down as many mysql queries as I can.

Im implementing partial caching, which writes .txt files for various modules of the site, and updates them on demand. I've came across one, that cannot remain static for all the users, so the .txt file thats written on the HD, will need to be altered on the fly via php.

Which is done via

flush();
ob_start();
include('file.txt');
$contents = ob_get_clean();

Then I modify the html in the $contents variable, and echo it out for different users.

Alternatively, I can leave it as it is, which runs a mysql query, which queries a small table that has category names (about 13 of them).

Which one is less expensive? Running a query every single time.... or doing it via the method I posted above, to inject html code on the fly, into a static .txt file?

Answer 1

Reading the file (save in very weird setups) will be minutely faster than querying the DB (no network interaction, &c), but the difference will hardly be measurable -- just try and see if you can measure it!

Answer 2

Try both and choose the one that either is a clear winner or if not available, more maintainable. This depends on where the DB is, how much load it's getting, and whether you'll need to run more than one application instance (then they'd need to share this file on the network and it's not local anymore).

Answer 3

Optimize your queries first! Then use memcache or similar caching system, for data that is accessed frequently and then you can add file caching. We use all three combined and it runs very smooth. Small optimized queries aren't so bad. If your DB is in local server - network is not an issue. And don't forger to use MySQL query cache (i guess you do use MySQL).

Answer 4

Where is your the performance bottleneck?

If you don't know the bottleneck, you can't make any sensible assessment about optimisations.

Collect some metrics, and optimise accordingly.

Answer 5

Here are the patterns that work for me when I'm refactoring PHP/MySQL site code.

The number of queries per page is absolutely critical - one complex query with joins is fastest as long as indexes are proper. A single page can almost always be generated with five or fewer queries in my experience, plus good use of classes and arrays of classes. Often one query for the session and one query for the app.

After indexes the biggest thing to work on is the caching configuration parameters.

Never have queries in loops.

Moving database queries to files has never been a useful strategy, especially since it often ends up screwing up your query integrity.

Alex and the others are right about testing. If your pages are noticeably slow, then they are slow for a reason (or reasons) - don't even start changing anything until you know what the reasons are and can measure the consequences of your changes. Refactoring by guessing is always a losing strategy espeically when (as in your case) you're adding complexity.