1

I'd like to put video statistics (number of likes, ratings, etc.) from YouTubers to the database. Here is my PHP code. It is very slow (200videos - 50s). Is there any way to load data into the database faster? (I am sorry for my bad English).

$user = $_POST['user']; // this is youtuber

$stmt = $pdo->prepare("SELECT id FROM yt_youtuberi WHERE user = ?");
$stmt->bindValue(1, $user, PDO::PARAM_STR);
$stmt->execute();
$row = $stmt->fetch();
$id_youtuberi = $row->id;

$request = 'https://www.googleapis.com/youtube/v3/channels?key=...&forUsername=' . $user . '&part=contentDetails';
$response = file_get_contents($request);
$searchResponse = json_decode($response, true);
$data = $searchResponse['items'];
$pid = $data[0]['contentDetails']['relatedPlaylists']['uploads'];

$nextPageToken = 1;
while (!empty($nextPageToken)) {
    if ($nextPageToken == 1) {
        $request = 'https://www.googleapis.com/youtube/v3/playlistItems?key=...&playlistId=' . $pid . '&part=snippet&maxResults=50';
    } else {
        $request = 'https://www.googleapis.com/youtube/v3/playlistItems?key=...&pageToken=' . $nextPageToken . '&playlistId=' . $pid . '&part=snippet&maxResults=50';
    }
    $response = file_get_contents($request);
    $searchResponse = json_decode($response, true);
    $data = $searchResponse['items'];
    $nextPageToken = $searchResponse['nextPageToken'];

    $i = 0;
    foreach ($data as $value) {
        $title[$i] = $value['snippet']['title'];
        $videoId[$i] = $value['snippet']['resourceId']['videoId'];
        $publishedAt[$i] = $value['snippet']['publishedAt'];
        $publishedAt[$i] = substr($publishedAt[$i], 0, 10);

        $request2 = 'https://www.googleapis.com/youtube/v3/videos?part=statistics&id=' . $videoId[$i] . '&key=...';
        $response2 = file_get_contents($request2);
        $searchResponse2 = json_decode($response2, true);
        $data2 = $searchResponse2['items'];
        $viewCount = $data2[0]['statistics']['viewCount'];
        $likeCount = $data2[0]['statistics']['likeCount'];
        $dislikeCount = $data2[0]['statistics']['dislikeCount'];
        $commentCount = $data2[0]['statistics']['commentCount'];

        $stmt = $pdo->prepare("INSERT INTO yt_videa (id_youtuberi, id_video, titulek, shlednuti, likes, dislikes, komentare, vytvoreno) VALUES (?, ?, ?, ?, ?, ?, ?, ?)");
        $stmt->bindValue(1, $id_youtuberi, PDO::PARAM_INT);
        $stmt->bindValue(2, $videoId[$i], PDO::PARAM_STR);
        $stmt->bindValue(3, $title[$i], PDO::PARAM_STR);
        $stmt->bindValue(4, $viewCount, PDO::PARAM_INT);
        $stmt->bindValue(5, $likeCount, PDO::PARAM_INT);
        $stmt->bindValue(6, $dislikeCount, PDO::PARAM_INT);
        $stmt->bindValue(7, $commentCount, PDO::PARAM_INT);
        $stmt->bindValue(8, $publishedAt[$i], PDO::PARAM_STR);
        $stmt->execute();
    };
}
Azeez Kallayi
  • 2,567
  • 1
  • 15
  • 19
BuMoRi
  • 45
  • 2
  • 6
  • Yes there is a way. Run several requests (or scripts) in parallel. – Solarflare Mar 19 '17 at 14:22
  • Maybe instead of doing a $stmt->execute every single iteration, collect the data and for every 100 iteration then do a mass insert. http://stackoverflow.com/questions/1176352/pdo-prepared-inserts-multiple-rows-in-single-query – MalcolmInTheCenter Mar 19 '17 at 17:13
  • OK, thx, I will try mass insert. – BuMoRi Mar 20 '17 at 07:13
  • MySQL running on a 10 year old computers will have no problem with 200inserts/s. The problem here is not MySQL, so a mass insert will not help. The problem here is your http-request. 0.25s is already quite fast for such a request. If you just do one http-request at a time, this will limit your throughput. So as I suggested, run multiple (http-) request at the same time, either by changing your script to run multiple requests at the same time, or by running multiple scripts (with 1 request each) at the same time. But be aware that the google api puts a limit on request/s that you can do. – Solarflare Mar 20 '17 at 11:03
  • OK, I wanted to run several scripts in parallel. About limit - you mean this? https://developers.google.com/youtube/v3/getting-started#quota – BuMoRi Mar 20 '17 at 14:09

0 Answers0