I'm creating an online chat.
Context (if needed):
So far I was using PHP/MySQL and AJAX to do the job but this is not a healthy solution as I'm stuck with a "pull" type application with concerns about scalability. I read about the "push" method alternatives and it seems that my choices are limited and exclude PHP. Websockets could be a very interesting option if it was integrated in every browser but that's not the case (and it seems that for most of those implementing it, it is disabled by default). Long polling would also be a candidate but it involves other issues like the number of concurrent open connections that may kill your web app too.
This is why, against my will, I think that my only viable option is to use server-side javascript (node.js + now.js would be my choice then).
This said, I may need to rethink the use of a database too. I need to keep stored data of each users and link these users to their submitted messages. In case of a chat engine driven by a push system, would MySQL still be a valuable choice then? I read about NoSQL data management and it seems that MongoDB would be a good addition to node.js.
My two questions:
Is there a reason I'm better off moving to a NoSQL system (which I need to learn from scratch) instead of MySQL (which I know already) in case of a real time web app?
Let's say that in MySQL:
Simple enough but how can I achieve that with MongoDB and its collections philosophy?
Thank you for your help.
Working with node.js/MongoDB is cool because Mongo's document structure is already JSONish, so you don't have to convert your queries to JSON. If you already know JavaScript, you have a headstart learning MongoDB. Mongo does scale for writes and reads pretty easily, the speed is pretty awesome, although I've seen some MySQL benchmarks on a single system that compare well to Mongo--it really shines when you start needing multiple boxes.
Assuming you have a separate messages collection, and you already know the id of the user you could just do: db.messages.find({user_id:ObjectId(...)});
Update: If you don't know the user id, then you need to do two queries, yes (unless you use an embedded array as recommended in the other answer--I would advise against that for this sort of use case, though, because you'll end up querying the entire document/list of messages even to display just a subset). Depending on your use case, obviously, if you have the username, you could also keep the user id handy, for situations like this. If it's client input giving the username that wouldn't work.
Update2: If you have unique usernames, you could make the username the _id for the users collection to avoid this issue. Most people would probably advise against this, and it has some definite drawbacks, such as making it harder to change a username.
You can't perform joins in MongoDB, so you can't achieve your second requirement. The Mongo way so do this would be either to nest messages within the user collection:
{ username: 'abc', messages: [...]}
Or use refId's, which is a kind of half-way house between joins and nested documents:
http://uk3.php.net/manual/en/class.mongodbref.php
In terms of switching from MySQL to Mongo, you don't necessarily need to ditch MySQL entirely. There are use cases where one is more appropriate than the other. You could use both for different parts of the system if it's appropriate to do so. Personally, I've used MySQL for a lot of things in the past, and I'm using MongoDB for a big project at the moment. I found the move very easy to make, because it's so easy to use the MongoDB driver, and the MongoDB site is very good for documentation on the whole.
You can convert to and from JSON with json_encode and json_decode from the front end, and you query and insert/update with arrays with MongoDB's PHP driver, so it's arguably more intuitive and easier to use than MySQL. It's just a question of getting used to it.