Filesystem or database?

[Nicholas the Italian]

Hello guys.
I thought I could share some doubts I'm having while projecting and developing a new website.
The reasons I'm posting here are:

• you may be so kind, and knowledgeable enough, to answer my questions
• phpBB3 behavior is what mainly triggered such questions.

Basically, I want to create php pages that are built putting pieces of HTML pages together - more or less like phpBB template system. I'm trying to do it in the most efficient way, as the website is quite populated and we're on a shared host.
These pages are mainly static (in fact they are pure HTML pages now), so it's "create once, use many times". Also, many of them are not "monolithic pages", but rather a list of "items" - jokes, for example, or aphorisms.

To cut the story short, there are basically two ways of doing things:

• having separate .html files containing all the items for that page, simply include()'d or file_get_contents()'d+echoed (wow, sorry for the neologisms) into php pages;
• having the items stored in a database and retrieved by PHP with SELECT queries.

Although I do know that database are very well optimized for handling such requests, I'd tend to think that a simple filesystem access should be quicker - at the very least because also database data is stored in and must be retrieved from the hard disk; plus, queries imply some other actions and additional checks, which should add overhead to operations.
I mean, databases are great for handling huge numbers of little pieces of information, or different operations over them (read, add, modify, etc.), searches, and performing complex operations on such data or intersection/concatenation/elaboration of data from different tables; but it shouldn't be better when it comes to just retrieving a bunch of lines from one table record.
BUT! phpBB3 stores entire stylesheets, images, and template files into the database, and uses it instead of files. Why is this done? For efficiency sake, or other reasons?

To sum it up, my questions are (generally speaking): if I have to include 50 out of 200 items in my page, and I have

• one database table containing one record for each item;
• one html page containing all 200 items;
• four html pages containing each 50 items (one of them contains the 50 I need);
• 200 html pages each containing one item;

what's more efficient:

• a 50-record select plus elaboration, echo, etc., or a straightforward single file inclusion?
• a 50-record select etc., or a file reading plus slicing it to obtain the 50 items I need?
• a 50-record select etc., or the inclusion of 50 html files? (this is out of curiosity, as I won't of course do this)

I hope I made some sense.
Any input will be welcome.

[Nicholas the Italian]

Wow, I'm overwhelmed by your answers...
Are you saying I'll have to do some benchmarks? Ugh...

[Prince of area51]

Hi Nicholas,

I aint an expert on this so I would recommend you do try some benchmarks first (as you will be doing it with the actual data, results may vary).

Now coming to reply, I think databases prove to be faster (and efficient) in handling what you have suggested. File access takes time and multiply it by a factor for busy sites, that lag factor increases, whereas for databases, queries are stored in internal cache, for example if it took 0.10 sec to generate a rowset, the next request for the same query will be returned in 0.002 sec (just an example).

Believe it or not, template engines do slow down the page generation time. But that lost time is covered by caches pages, etc so obviously the most efficient method is the first one that you have mentioned which uses straight echos.

All what I have suggested is just from my experience of working with PHP/MySQL, there is no need to point a gun at me and push the trigger if I get something wrong

[Nicholas the Italian]

Thanks for sharing, I'll think about it.

Just one thing I don't quite understand:

Believe it or not, template engines do slow down the page generation time. But that lost time is covered by caches pages, etc so obviously the most efficient method is the first one that you have mentioned which uses straight echos.

What do you mean? I made three comparisons, between the same select query + elaboration + echo and 1) single file inclusion (straightforward, no elaboration), 2) reading a "big" file and displaying only the needed part, 3) reading and including tens of different files. Are you saying db should be faster also in case 1?
That would quite surprise me. Of course there's caching involved, but after all also read file go into RAM memory so the second access would be much quicker.

Mmm. Definitely worth some testing.

[Prince of area51]

- idea dropped -

[Cap'n Refsmmat]

A decent operating system should cache frequently accessed files in memory.