I have a text based game site (Mafia game) written in old php/mysql style. I have no to very less knowledge of PHP/MYSQL i am learning it though. So i am having problems with one of file which reloads every 5 second via ajax , it contains few Mysql query which checks messages, forum messages,transfer,attacks etc and depending on the check it shows alert to users if they get any new message,forum messages,transfer,attacks etc. My site is using VPS right now and if i set the refresh rate to 5 seconds it overlaods the VPS within few minutes so i have to set the refresh time to 20 seconds or more. I would like to know if there is any problems with query or any suggestion to optimize query/php code. Below is code of my file ajax.php which needs to be reloaded every 5 seconds
<?php
include("funcs.php");
global $tab, $time, $id, $tru, $old;
$round=$_GET['tru'];
$tO=0;
$moFo='r'.$round.'_mafiosi';
$brd=$tab['board'];
$query="select msg,atk,ivt,transf from $moFo where id='$id'";
$result=mysql_query($query);
$ans=mysql_fetch_assoc($result);
foreach($ans as $key=>$value)
{
$tO+=$value;
}
$rtn='#'.$mafioMsg;
echo "$tO$rtn";
?>
#and below is the jquery/javascript i am using :
<script type="text/javascript" >
var onOff=false;
var replyText='';
window.onload=mainF;
function hideRedNotif()
{
document.getElementById('redNotif').style.display='none';
}
function mainF()
{
fetchNotif();
Updtr=window.setInterval(fetchNotif,25000);
}
function toggleNotif()
{
document.getElementById('redNotif').style.display='none';
if(onOff==false)
{
document.getElementById('parentReply').style.display='';
onOff=true;
}
else
{
document.getElementById('parentReply').style.display='none';
onOff=false;
}
}
function getAjxObject()
{
try {
var o=new XMLHttpRequest();
}
catch(exception)
{
var o=new ActiveXObject('Microsoft.XMLHTTP');
}
return o;
}
function fetchNotif()
{
roundN=document.getElementById('roundName').value;
var o=getAjxObject();
o.open('GET','notifAjx.php?openSes=in&&tru='+roundN,true);
o.onreadystatechange=execute;
o.send();
function execute()
{
if(o.readyState==4 && o.status==200)
{
var countF=0;
resp=o.responseText;
rsp=resp.split('#');
dom=document.getElementById('notifM');
dom.innerHTML=rsp[0];
//document.getElementById('chatRoller').innerHTML=rsp[1];
//if(rsp[1]!=replyText )
//{
//document.getElementById('redNotif').style.display='';
//replyText=rsp[1];
//}
}
}
}
function sendReply()
{
var o2=getAjxObject();
roundN=document.getElementById('roundName').value;
m=document.getElementById('replyText').value;
msg='&&reply=1&&msg='+m;
url='notifAjx.php?tru='+roundN+msg;
o2.open('GET',url,true);
document.getElementById('replyText').value='';
o2.onreadystatechange=execute;
o2.send();
function execute()
{
if(o2.readyState==4 && o2.status==200)
{
}
}
}
</script>
UPDATE- Thanks to everyone for checking my issue, I took screenshots of my DB tables please check if it helps or let me know what else should i provide.
I will appreciate any suggestion/help.
Thanks & Regards Prashant
please check for indexing in the table '$moFo' and check whats the volume of the data you are dealing with, if its high then do archive them or use sharding.
I expect you may have issues because if your asynchronous request takes more than 5 seconds, you'll start to get a backlog. It might sound counter-intuitive, but I recommend making your asynchronous requests a bit more synchronous:
Currently you're using setInterval to run your check every five seconds, regardless of whether a response has come back. What you could do instead is use setTimeout to get it started and then set another timeout when your response has come back, whether it was successful or not. This way, your responses will never start to overlap.
In practice:
Change your mainF()
function to be
function mainF()
{
fetchNotif();
Updtr=window.setTimeout(fetchNotif,25000);
}
Then change your fetchNotif() execute()
function to set another timeout once it's processed
Updtr=window.setTimeout(fetchNotif,25000);
You may want to wrap that one in an if
to check for readyState == 4
but don't check for status == 200
because you probably want it to try again even if the previous attempt failed.