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?
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.