Will polling from a SQL DB instead of a file for chat application increase performance?

Question

I'm working on a chat application which I would love to use a SQL db for.

My problem is, after a few google searches, i have people telling me from one site, that using a DB would be much slower then using a normal file (e.g Text or JSON file), but then on some other sites, people are saying the complete opposite. And I don't know about you guys, but when it comes to creating web apps for users, the users always come first.

So as much as I'd love to use a SQL DB as 1.) I have good experience with it and 2.) it allows me to make the application much more cooler (more features). but if it would slow things down on the users end (a noticeable lag), then its a no-no.

Either way, I will be "polling" the server continuously with AJAX and PHP to check the file/DB (for new messages, contact requests, ect ect).

Also, incase your wondering, the application wont be like a 1-to-1 chat, it will have "rooms" where multiple users can join and talk with all users joining in. The users will also be able to request a "private chat" with another user, where a 1-to-1 connection opens up.

So, MySQL Database OR a boring TEXT/JSON/OTHER file, in regards to performance?

Oh, one more thing, I don't want to use any third party libraries or APIs. Hate relying on other peoples work (been let down to many times).

Diocletian

Does your thing work like IRC, that is, you can only see messages sent after you joined? – Codee

Yeah. The way I'm thinking of doing it is, every "room" will have a row in a table, with a column that contains all the user id's of the users who are currently in the room. And on each AJAX call, if the users id is in random_room then they will check for new messages in that room and so on... – Diocletian

it is said that not tool but it's proper use will increase performance. – Nmr

Answer 1

If you're looking to implement an IRC clone, I think you've chosen all the wrong tools.

The best way to do this would be to write a custom HTTP server that handles everything in memory. No databases, no constant polling of files. When a message arrives, you simply loop through the correct in-memory list and dispatch the message to other users. For the browser to server connection, I suggest "Comet" (with web sockets for browsers that support them, if you're feeling up to it).

PHP likely isn't the language of choice for this, because pretty much all work done with PHP is based on traditional short, isolated requests. For a long-running process which serves multiple clients in real time, I'd suggest something like Python or Node.js.

Codee

Can we use PHP Redis/Memcached instead of custom HTTP in-memory server? – Narrows

@kta: Can you? Sure. Should you? No, you should not use PHP for anything. – Codee

Answer 2

You don't really want to be storing chats in files, that can create a management nightmare, I would recommend you go with MySQL and to make sure it works probably go with Sockets instead of AJAX polling, Sockets will scale really well.

However there isn't much around about how you can integrate socket based chats with MySQL.

I have done a few tests and have a basic example working here: https://github.com/andrefigueira/PHP-MySQL-Sockets-Chat

It makes use of Ratchet (http://socketo.me/) for the creation of the chat server in PHP.

And you can send chat messages to the DB by sending the server JSON with the information of who is chatting, (if of course you have user sessions)

Maquis