File access speed vs database access speed

Question

The site I am developing in php makes many MySQL database requests per page viewed. Albeit many are small requests with properly designed index's. I do not know if it will be worth while to develop a cache script for these pages.

1. Are file I/O generally faster than database requests? Does this depend on the server? Is there a way to test how many of each your server can handle?

2. One of the pages checks the database for a filename, then checks the server to see if it exists, then decides what to display. This I would assume would benefit from a cached page view?

Also if there is any other information on this topic that you could forward me to that would be greatly appreciated.

Answer 1

If you're doing read-heavy access (looking up filenames, etc) you might benefit from memcached. You could store the "hottest" (most recently created, recently used, depending on your app) data in memory, then only query the DB (and possibly files) when the cache misses. Memory access is far, far faster than database or files.

If you need write-heavy access, a database is the way to go. If you're using MySQL, use InnoDB tables, or another engine that supports row-level locking. That will avoid people blocking while someone else writes (or worse, writing anyway).

But ultimately, it depends on the data.

Answer 2

It depends on how the data is structured, how much there is and how often it changes.

If you've got relatively small amounts, of relatively static data with relatively simple relationships - then flat files are the right tool for the job.

Relational databases come into their own when the connections between the data are more complex. For basic 'look up tables' they can be a bit overkill.

But, if the data is constantly changing, then it can be easier to just use a database rather than handle the configuration management by hand - and for large amounts of data, with flat files you've got the additional problem of how do you find the one bit that you need, efficiently.