Filesystem or database?

Want to chit chat about anything, do it here ... posting here won't increase your post count (or shouldn't!). Please do not post any "phpBB" specific topics here unless they do not fit into the category above. Do not post bug reports, feature or support requests!
Forum rules
Please do not post any "phpBB" specific topics here unless they do not fit into the category above.

Do not post bug reports, feature or support requests! No really... Do not post bug reports, feature or support requests! Doing so will make Bertie a very sad bear indeed. :(
Post Reply
User avatar
Nicholas the Italian
Registered User
Posts: 659
Joined: Mon Nov 20, 2006 11:19 pm
Location: 46°8' N, 12°13' E
Contact:

Filesystem or database?

Post by 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 :P
  • 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.

User avatar
Nicholas the Italian
Registered User
Posts: 659
Joined: Mon Nov 20, 2006 11:19 pm
Location: 46°8' N, 12°13' E
Contact:

Re: Filesystem or database?

Post by Nicholas the Italian »

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

User avatar
Prince of area51
Registered User
Posts: 133
Joined: Mon Jun 27, 2005 8:46 pm
Location: Manchester, UK
Contact:

Re: Filesystem or database?

Post by 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 :?

User avatar
Nicholas the Italian
Registered User
Posts: 659
Joined: Mon Nov 20, 2006 11:19 pm
Location: 46°8' N, 12°13' E
Contact:

Re: Filesystem or database?

Post by Nicholas the Italian »

Thanks for sharing, I'll think about it.

Just one thing I don't quite understand:
Prince of area51 wrote: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.

User avatar
Prince of area51
Registered User
Posts: 133
Joined: Mon Jun 27, 2005 8:46 pm
Location: Manchester, UK
Contact:

Re: Filesystem or database?

Post by Prince of area51 »

- idea dropped -

Cap'n Refsmmat
Registered User
Posts: 219
Joined: Tue Jan 25, 2005 11:31 pm

Re: Filesystem or database?

Post by Cap'n Refsmmat »

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

Post Reply