Mysql database storage?

Question

Hello;
I would like to know how the mysql database schema is designed for storing private chat messages between 2 users. Like if i insert the every line of a chat as a new record, database will grow very huge and it may lead to server crash or slow working.

I tried to store the full message in a xml file and later when chat is completed i dumpted that file into database. But here i have a problem at maintaining the history of the chat. And this process will really slows the app.

Or can i store the messages at php sessions and finally dump into database. Because in some article i read that max storage of php session is 128MB

I'm still worried about the Database structure.

So whats the best way to store? I'm thinking how Google [Gtalk] will store the chat message? Which is very fast and user friendly.

So please suggest me the best way.

Thank you..

Answer 1

This is done in several ways. For example Facebook has a separate database for different countries, they uses a dedicated MySQL server, and servers are configured in different ways, suppose they have 8 dedicated MySQL server in Singapore, so they will have 2 servers to control country A B C or more other 2 server will control country D E F or more and so on. This helps to reduce load on each server. Now when you are chatting its is done via MySQL or any other database. There will be a threshold limit such as last 10 messages will be stored in the database and rest of the messages will be converted to a XML file and the link of the XML file will be stored in a database this helps to retrieve all previous message.



I don't think saving data in PHP session will be good because all those data are stored on RAM so it will reduce your server memory, and servers required a huge memory to perform smoothly. So if you have 10 user chatting simultaneously at a time and if each of them have 5MB of data then total will be 5*10=50MB. So it will not be a good technique.

Answer 2

>> I'm thinking how Google [Gtalk] will store the chat message? Which is very fast and user friendly.



Those are not servers. Those are MEGA Servers specially built for handling Load of data of million people.



On the other Hand, there is no problem with Number of records if you design the structure of the table and use it efficiently.



Also, I don't think that the method which you suggested (store temporary in an XML/TXT file and then dump it) will slow down your Server.

That is a nice way to deal with this problem... ;)

Answer 3

Number of records does not matter neither does size, mysql is prepared for all that. The only thing that could slow you down when there's an huge amount of records is the queries you run on them. If you properly index the records then queries shouldn't be too much of problem with huge amounts of records.



http://www.tizag.com/mysqlTutorial/mysql-index.php

Answer 4

there are various webhosts accessible that grant what you're searching for.. different than the SSH get right of entry to.. bluehost facilitates SSH get right of entry to as area of their service.. additionally 247-host facilitates SSH get right of entry to even regardless of the indisputable fact that it relatively is an added characteristic you ought to pay for. yet the two a type of webhosts i'd particularly advise. they're low priced, solid, and endless!

Answer 5

searching in my blog :)

http://www.yudhacomunity.blogspot.com/