asked on

Comparison: Caching data to an external file or retrieving from MySQL database?

When dealing with moderate amounts of information, such as script settings (roughly 50-75 settings), etc... which is more effecient: writing the data to an external file to be included from there-on-out, or quering the database to retrieve the information? I ask becuase I'd like to get my page query count as low as possible, and I could eliminate one more query if I just included the settiings from a file, but if it's better to query it, I'll do that. Any examples, documentation, anything?

ASKER CERTIFIED SOLUTION

matt_mcswain

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

SOLUTION

_Marcel_

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

MaybeItsJeremy

ASKER

Really sounds like I need to go with writing the data to a file... I didn't think about the server being able to cache the file, that will be perfect. One other question before I split the points here, let's say we have a news site with a HUGE amount of traffic (as in several thousand page views per day) would it be smart to instead of query the database for the content of an article, just retrieve the data from a cached file (in addition to having it stored in the DB for back-up)? I was thinking a method could be set up in the AdminCP clean up to check the time stamp of the file (just putting it in the file name for the cache file, like timestamp.01.xml) and if it's over a week or two old, then delete the cached file, as most people will not be going to it any longer and will be concetrating on recent news. And if someone were to go to the old article, it would just come from the DB.... does that sound like a good plan?

_Marcel_

Line matt_mcswain already said, serving a static page is often better than serving a dynamic page with mysql requests. The first gain would be that the database-server would be (far) less questioned, and the second gain would be that the webserver could easily cache the page, and if it is requested often, would be able to serve it from memory, again lessening your I/O. Normally you would have yet another advantage: since the page is static it would also be easier for the browser to cache (with dynamic pages it is often difficult to determine if the page has been changed). In your case this would not benefit you much, because I guess most people wouldn't be reading the same news more than once...

So I think your plan would be right (unless you have of course plenty of money and don't care about server capacity).

matt_mcswain

You just have to decide how much time you have to put towards caching pages; how much is worth your time. On a site like that, it would definitely be worth auto-generating the home page at least when an update is made; instead, for instance, pulling the top story, top related stories, and top stories for other categories, etc from the database; however your site works. Is it worth your time to implement the fuctionality for each section of the news? or even each news story itself? Again, I'm not talking about caching in memory(like _Marcel_ said, let your OS worry about that), I'm talking about generating html files. If you wanted to go nuts, you could created a very powerful database driven site, that never accesses the database or php on user request. I wouldn't because changing the layout would be a mess, and I rely on Smarty for that type of thing. But instead, slowly minimize the database calls on the frontend until you're happy with the performance.

Oh, and pulling old articles from the database and just auto-gerenerating fresh ones, sounds very reasonable.

MaybeItsJeremy

ASKER

Thanks guys. The advice is much appreciated. :)