Best way to store chat messages and files

Question

I would like to know what do you think about storing chat messages in a database?

I need to be able to bind other stuff to them (like files, or contacts) and using a database is the best way I see for now.

The same question comes for files, because they can be bound to chat messages, I have to store them in the database too..

With thousands of messages and files I wonder about performance drops and database size.

What do you think considering I'm using PHP with MySQL/Doctrine?

Answer 1

I think that it would be OK to store any textual information on the database (names, messages history, etc) provided that you structure your database properly. I have worked for big Web-sites (multi-kilo visits a day) and telecom companies that store information about their users (including their traffic statistics) on the databases that have grown up to hundreds of gigabytes and the applications were working fine.

But regarding binary information like images and files it would be better to store them on the file systems and store only their paths on the database, because it will be cheaper to read them off the disks that to tie a database process to reading a multi-megabyte file.

As I said, it is important that you do several things:

1. Structure you information properly - it is very important to properly design your database, properly divide it into tables and tables into fields with your performance goals in mind because this will form the basis for your application and queries. Get that wrong and your queries will be slow.

2. Make proper decisions on table engines pertinent to every table. This is an important step because it will greatly affect the performance of your queries. For example, MyISAM blocks reading access to the table while it is being updated. That will be a problem for a web application like a social networking or a news site because im many situations your users will basically have to wait for a information update to be completed before the will see a generated page.

3. Create proper indexes - very important for performance, especially for applications with rapidly growing big databases.

4. Measure performance of your queries as data grows and look for the ways to improve it - you will always find bottlenecks that have to be removed, this is an ongoing non-stop process. Every popular web application has to do it.