json justin slow

Question

People are starting to complain about my website speed, that it is slow. I need your help to identify the problem. I have been searching around the internet like crazy after a better solution but without success. I am trying to get streamname and how many visitor the streamer have from justin.tv/twitch.

Currently I am using the getting-started code from their own API wiki page. It is however, very slow. I have 52 streams stored in my mySQL database which I put into an array and then using json to parse the data.

<?php
$result = mysql_query("SELECT streamname FROM streams") or die(mysql_error());

$ids=array(); 
while($row = mysql_fetch_assoc($result))  
{
    $ids[]=$row["streamname"]; 
}

$stream_list = implode(",", $ids);
$mycurl = curl_init();

curl_setopt ($mycurl, CURLOPT_HEADER, 0);
curl_setopt ($mycurl, CURLOPT_RETURNTRANSFER, 1); 

//Build the URL 
$url = "http://api.justin.tv/api/stream/list.json?channel=" . $stream_list; 
curl_setopt ($mycurl, CURLOPT_URL, $url);

$web_response =  curl_exec($mycurl);
$results = json_decode($web_response); 
foreach($results as $s) 
{   
 echo "<a href='stream.php?watch=" . $s->channel->login . "'>" . $s->channel->login . " " . $s->channel_count . " viewers</a><br />";
}
?>

UPDATE. Yes I use my MySQL to set a type of category of each channel. I tried without this too, and the speed difference is not that much. So it's still the json that takes time to load. Here is how I use the MySQL

   $result = mysql_query("SELECT streamname FROM streams WHERE race = 'terran' OR race = 'protoss' OR race = 'zerg'") or die(mysql_error());

$ids=array(); 
while($row = mysql_fetch_assoc($result))  
{
    $ids[]=$row["streamname"]; 
}

$stream_list = implode(",", $ids);
$mycurl = curl_init();

curl_setopt ($mycurl, CURLOPT_HEADER, 0);
curl_setopt ($mycurl, CURLOPT_RETURNTRANSFER, 1); 

//Build the URL 
$url = "http://api.justin.tv/api/stream/list.json?channel=" . $stream_list; 
curl_setopt ($mycurl, CURLOPT_URL, $url);

$web_response =  curl_exec($mycurl);
$results = json_decode($web_response); 
echo "<div id=\"tab1\">";
foreach($results as $s) 
{
    // get race
    $sql = mysql_query("SELECT race, streamname, name FROM streams WHERE streamname = '" . $s->channel->login . "'") or die(mysql_error());

    $row = mysql_fetch_array($sql, MYSQL_BOTH);
    $race = $row['race']; // race
    $streamername = $row['name'];

    echo "race: " . $race . " <a href='stream.php?watch=" . $s->channel->login . "'>" . $row['name'] . " " . $s->channel_count . " viewers</a><br />";

}
echo "</div>";

Answer 1

Most likely the problem is, that polling the data from api.justin.tv takes quite long (compared to the rest f your code). A http connection takes some time. While the data is being pulled, there is nothing that could be displayed so the user experiences long waiting times.

There are several solutions. You should not load the api information every time a user opens the website but periodical. You could download the data every 5min or use a cron job to pull it in the background every few minutes or seconds (depends on how fast the values change) and store the data in a database table.

If the data is stored on your server (even on the slow file system) it will be much faster.