At our school we have seen performance problems with chat (which of course we know), but we have developed a possible fix.

The general problem that we reduced, is that chat logs (history) is kept in the same table as messages from active conversations. So ever refresh, each client is doing a select on the chat table, which contains all chats. In our case that adds up to +400k rows, so even a simple select takes quite some overhead.

Our solution was to split the chat into 2 table. One is the normal 'chat_messages', and the other is 'chat_messages_current'. Clients query against 'chat_messages_current'. When somebody talks, the message is written to both tables. Every cron cycle, any message older than 8 hours is deleted from 'chat_messages_current'. Basically that means that the frequent select will be against a table that will rarely have more then a few hundred messages in it.

For us, this decreased loads by about a factor of 10 on our MySQL server.

-eric