Question: I'm trying to create a chat for a site and I'm wondering if the db solution is the best one in case of a large amount of users.

In case the db is an acceptable solution I would like to know which is the best way to design it:

1. Is one table enough to store all the messages from all the users? Do I have to store each message everytime a user send it (like a simple "hello" in one record)?
2. Do I have to create a separate tables for each chat?

Obviously assuming that the indexing and the partitioning are made.

I'm scared about the performance at this stage (db level). Then I can concentrate better to the middleware section and manage it there.

Answer: 
MySQL is not ment for realtime
------------------------------

MySQL is clearly not the best solution for a chat: You have no reason to work on years of backlog to get the most current messages, nor do you want to fire a query per second for every active user. The overhead of a relational database is not to be ignored.

Check out redis
---------------

I would go with [redis](http://try.redis.io/): It is an advanced key-value storage with capabilities of realtime interaction (PUB/SUB), automatic expire, and it is definitely faster than MySQL when it comes to loads of simple data.

**Edit:** Yes, it has all it´s data in ram. A Gb of ram would be enough for about every book in every libary of the world. That said, MySql also uses ram caching (query caching). And redis is ACID. (Oversimplified: You can enable saving to disk.)

MySql hints, if you stick to it
-------------------------------

If you yet decide to go with MySQL, you will have to write every single line into the DB for others to be visible. More explicit, you need a commit on every message. Make sure that you have some sort of cleanup mechanism, e.g. a cronjob moving all messages older than a day into some archive-tables.

cache!
------

Imagine 100 users in your room, each checking every 3 seconds for new messages. 300 queries per second? (Ok, decent servers can handle this, but you asked for a good solution) Go another way: Have a memcached /redis-saved flag "Last message id". Change it every time someone writes something to the chat. Now make the client submit it´s last-known message id. If you´ve got a hit, exit immediately even without MySql starting up. If you are really good, you can make PHP even make to return the appropriate ETag.

Long polls
----------

As it comes to the frontend client: **Do not fire a reload or ajax request every n seconds!** Inform yourself about Websocket and **long polls**. That is a technique where the browser opens a site that will not immediately return a result, but will keep the connection open untill there is something to report (or timeout occurs)

Edit: OP´s comment asking what programming language to use
----------------------------------------------------------

That depends on your knowledge. I would go with PHP and redis, but that is because I know them well. If you prefer Java, use it. If you have no preference: Java is more versatile, php is easier to start learning. There is no objective one-size-fits-all-answer.
Determine what features you want. A few I can think of right off that might make a difference:

* Do you want to store the stream at all? (If not, then your DB needs are minimal.)
* How much of the stream do you want accessible to users? One day? A week? All of it? (You can offload messages periodically to maintain history for yourself, but also keep the publicly-accessible DB small.)
* How many messages per minute / hour / day can a user post? (Hint: not as many as they want)
* Do you want threading, or just one long stream that everyone can see? (Increases complexity)
* Do you want users to be able to send private messages? (Increases complexity)
* How often is the chat refreshed in the browser? (The more it's refreshed, the larger the load on the server)