Flat file vs database - speed?

Question

I'm making a chat program, and I need a place to store messages. The client would contact the server every x seconds with the last received message id, and the server would find all messages with a id higher than that, in the rooms that the client has joined.

As I'm not going to store things forever, I'm thinking of using flat files (one per room, as well as direct messages) with only the last 40 or so messages. However I think with comparing numbers a database would be faster.

What method of data storage should I use?

Answer 1

The flat file may be a bit faster, but it will end up being more buggy in the long run, because instead of just doing a SELECT * FROM messages WHERE room=nnn AND ID > yyy, you will have to load the file, parse it, scan each row for the message ID, go to the right message, and then read it out.

That's only the first problem. A text file doesn't support writing by multiple users (what if two people are posting to the same room at the same time?), and can easily become corrupt.

All things considered, I'd say it's better across the board to use a DB, even if it's something simple like SQLite, which has excellent PHP support. However, consider the many-users condition, MySQL is probably a much better choice. Also, MySQL has excellent caching capabilities, so most of the time, the recent data will come directly from RAM, and will be served faster than you could scan a text file in PHP either way.

Answer 2

Seems you don't have to keep your chat history at all, so why are you considering to use a permanent data storing(database or flat-file) method? You can do this using in memory caching software like Memcashed which faster than database or flat-file.

Answer 3

In general, a database would be far faster, since an index would let you go directly to the records the server has to send.

OTOH, with just 40 or so messages, it's quite likely that would fit in RAM, and with so few records, even the simplest linear-search routine would be far faster than a single HD access.

Still, using a database is so much easier, I'd use it for simplicity and not for speed. Also, if you have a lot of simultaneous rooms, writing it all yourself means a lot of opportunities for small, trivial bugs that delay development unnecessarily.

Just go with a database.