How to use PHP to cache AJAX refreshes (MYSQL)

Solution for PHP, Ajax and MYSQL Caching

[filiped]

I've developed a very simple chat engine with PHP, MYSQL and AJAX and now I'm having huge performance issues above 650 users online.

Like I said, it's a very simple engine that keeps requesting this address throught AJAX every 10 seconds:

This script opens a MYSQL connection with a querry requesting the last 10 messages. Even if there isn't any new messages, the file returns all of them.

PROBLEM: my main problem here isn't about wasting bandwith in this proccess... the main problem is opening mysql connections every refresh.

Is there any easy solution to cache the return from the script without making any mysql connections?

My server lacks of a good mysql server (and sum this with my not optimizaded script), so I was wondering if I could make a cron job to save the return of this query in a HTML file and then point AJAX to read that file.

Is this possible? Can you guys just point me a direction to go?

[edited by: jatar_k at 1:40 pm (utc) on June 22, 2009]
[edit reason] no urls thanks [/edit]

[andrewsmd]

This is just and idea, but what about current messages table that has a randomly generated key (not the primary but a randomly generated one). then you could either use a session variable or a cookie and save that key. When a new post is entered, that table gets updated and the key gets changed again. Now on the client side through your ajax, you just request that key, if it's the same then don't repost anything, if it's not, then get the new data. I know it's not the best solution but it would cut down on the mysql overhead instead of requesting thoses posts all the time it would only request them when it needs them. Just an idea.

[andrewsmd]

I would also suggest every 10 seconds is a little extreme, even if you go every 20 seconds, you cut your overhead in half.