php - File vs database for storage efficiency in chat app

Question

I'm working on a simple AJAX chat add-on for my PHP application so I can provide real-time support to my users. I'm currently using a MySQL database storing the text, timestamp and user_id of the person who is chatting. I got to thinking about how I could optimize my chat and I got to thinking about removing the need for the SQL database.

My question, would it be more efficient to use fwrite() to append extra data to a PHP file to store the same information rather than creating an SQL connection to retrieve new posts to the chat? I know how I would accomplish this effectively, I'm just trying to figure out which way would be more efficient.

I've looked a little bit at SQLite as well; would that be a better alternative than using a MySQL database?

Answer 1

Database Management Systems (DBMS) exists because it's not as easy as it seems to store and access data the right way.

Storing data in a file implies access concurrency issues. When the file will grow bigger, you will have to face important memory usage or write a lot of code to load just what you need. It will also be quite hard to do basic operations like filtering (SQL WHERE clause) or updating a row. BTW, changing the data structure promises to be error-prone. I simpler words: you will have to write a lot of code and face a lot of bugs.

IMO, not using any kind of DBMS is recreating the wheel. However choosing the right one is important.