live chat: saving directly in database or text file?

Question

im going to use ajax/comet to create a chat. and i want to store the chat conversation.

every keypress will trigger an event that sends to backend.php that will store the letter. i wonder if i should store it directly in the database or in the textfile.

you dont have to consider the details for exactly how this chat's architecture will be. just that every letter pressed will be stored immediately.

i thought that if storing in mysql (im using php on a comet server) then there will be a lot of queries, just for one letter each. and if a lot of users are chatting, then it will be a heavy load with UPDATE and SELECT queries.

will storing the conversation in text files be easier for the server? just open it, append the letter and close it with php.

what do you think?

Answer 1

Question: live chat: saving directly in database or text file?

Answer: If all you have is random chat text, a text file is probably perfect.

Justification: What possible SQL query would you ever use on this data?

At best you might use something like grep to scan the file.

Why bother with a database unless you have really complex data with a lot of interesting attributes?

Example:

select username,count(*) as most_active_users from chatroomxxx group by username limit 0,10;

Python:

user_fq = collections.defaultdict( int )
for path, dirs, files in os.walk( 'path/to/chat/logs' ):
    for fn in files:
        with open( os.path.join( path, fn ), "r" ) as source:
            for line in source:
                user, timestamp, text = line.split('\t')
                user_fq[user] += 1
fq_user = collections.defaultdict( list )
for user, fq in user_fq.items():
    fq_user[fq].append( user )
top = []
for fq in sorted( fq_user, reverse=True ):
    top.extend( fq_user[fq] )
    if len(top) > 10: break

The point is not that Python is as terse as SQL. The point is that the query is relatively simple data gathering from text files.

And simple data gathering from text files is FAST. Far, far faster than a database. And with essentially zero overhead.

Just write the lines to files. Scan the files with simple scripts. No overhead.

Answer 2

There will be as many file operations as sql operations. Files may be quicker for some purposes, but databases will scale much better.

I'm surprised you're firing every keypress - do you know how fast some users type??? With Ajax being asynchronous (by default), you will quickly find that your requests are stacking up and this will delay the message being delivered to the chat partner as well as place huge load on your server.

You could downgrade to sending after every word, or even on a return keypress.

Answer 3

I would not send every letter over the network, this will needlessly generate a large amount of network traffic and database/file writes. Instead send the message when the user has committed to it by pressing a Send button or the Enter key.

Storing in a database makes much more sense than a file, as you will need to be able to index the data for quick retrieval. Consider that you will need to retrieve data in groups, not as it was sequentially added to the file.