I have a JavaScript script, that uses Ajax to call PHP page, the php page fetch the new updates from MYSQL database..
Ajax request is repeated every one second .
Does it a good practice to access database every second ? Is there any other suggestion to improve this operation ?
I want to make something like twitter real-time updates, each second I will retrieve only the new data, if there is no new data, nothing will return from database ( but still access the database every 1 sec)
Make sure that you are not using setInterval but are using callbacks when your ajax call is successful or else you might have a backlog of calls that get stuck.
Also you might want to utilize some long-polling methods like websockets, comet, or server-sent-events.
I would not design a dependency between my UI and my database directly.
It's probably reasonable (depending on the app needs) to have the UI call a web service once a second. That web service can decide whether the correct strategy is to return a cached result or get new data from the database (if you have dozens of calls from dozens of browsers per second, you certainly don't want to call MySQL dozens of times in that one second.).
Having the UI call a web service and the web service interact with the database decouples the decisions "how often should the UI ask for more data?" from "how often should I check my database for an update?"
It is never good practice to be pulling from a database repeatedly in such a short period of time, and I've never encountered a situation where it actually needed to be done. Is there a hard requirement to have updated information guaranteed to within the last second? If not, change the time between polling to once a minute, minimum. That alone will provide a major speed boost, and will reduce your server load significantly.
Alternatively, if you need up-to-date information whenever a user performs some action, just poll the database at the beginning of that action.
Probably it is better to use SJAX (Synchronous ) if you really have to make request in every second. This will help reduce backlog of calls. because it makes second request after it get the first response. for example:
funftion get_data(){
if (window.XMLHttpRequest){// code for IE7+, Firefox, Chrome, Opera, Safari
xmlhttp=new XMLHttpRequest();
}
else{// code for IE6, IE5
xmlhttp=new ActiveXObject("Microsoft.XMLHTTP");
}
xmlhttp.open("GET", url ,**false**);
xmlhttp.send(null);
return xmlhttp.responseText;
}
use false in open() method to make it synchronized