douxuanpa8298 2014-01-08 19:26
浏览 27
已采纳

mySQLi插入脚本永远

Here's basically what's stored inside my PHP script

$query = $db->query("
SELECT *
FROM `media`
WHERE `id` > '$last_id'
AND `accounts_used` != ''
ORDER BY `id` ASC
LIMIT 100
");

foreach($query['results'] as $row) {
    $last_id = $row['id'];
    $accounts_used = explode(", ",$row['accounts_used']);
    $db->connect();
    foreach($accounts_used as $liked_account) {
        $account_id = str_replace(" ","",$liked_account);
        $media_id = $row['id'];
        $insert_array = array(
            "account_id" => $account_id,
            "media_id" => $media_id,
            "timesent" => "0000-00-00 00:00:00"
        );
        $db->insert("media_likes", $insert_array);
    }
    $db->disconnect();
}

An example of the $row['accounts_used'] is like so

61519, 65894, 63561, 61718, 63567, 66924, 66979, 66972, 66637, 66295, 66842, 64775, 51898, 64631, 65044, 67226, 67582, 66861, 51543, 61564, 65597, 66863

On average the accounts_used row contains around 1000/2000 unique ID's which are separated by commas, I want to explode based on the comma and insert each ID into another table.

media_id contains an integer value roughly around 5 digits long and account_id is about the same

This script is taking around 3 minutes to complete, any specific ways to improve such functionality? or a quicker way to do it?

  • 写回答

2条回答 默认 最新

  • dsarttv037029 2014-01-08 19:32
    关注

    A few things I can see that might help off the bat:

    1 - you are opening and closing your db connection after each row of data you process (don't)

    2 - you can use a prepared statement to have the mysql connection "pre-compile" your insert statement

    3 - use a transaction around the inserts so your indexes only get updated once

    本回答被题主选为最佳回答 , 对您是否有帮助呢?
    评论
查看更多回答(1条)

报告相同问题?

悬赏问题

  • ¥15 MATLAB怎么通过柱坐标变换画开口是圆形的旋转抛物面?
  • ¥15 寻一个支付宝扫码远程授权登录的软件助手app
  • ¥15 解riccati方程组
  • ¥15 display:none;样式在嵌套结构中的已设置了display样式的元素上不起作用?
  • ¥15 使用rabbitMQ 消息队列作为url源进行多线程爬取时,总有几个url没有处理的问题。
  • ¥15 Ubuntu在安装序列比对软件STAR时出现报错如何解决
  • ¥50 树莓派安卓APK系统签名
  • ¥65 汇编语言除法溢出问题
  • ¥15 Visual Studio问题
  • ¥20 求一个html代码,有偿