我的Mysql很弱

我必须根据我编写的以下脚本的某些条件更新答案表的问题 ID，但由于数据量巨大，执行时间超过 700 秒，

谁能帮我解决这个问题

function UpdateAnswer()
{
ini_set('max_execution_time', 800);
        $users= DB::select(DB::raw("select * from users where role!='admin_dd' and role!='sales_dd' "));

foreach($users as $user)
       {
         $answers= DB::select(DB::raw("select * from answers where  user_id='$user->id' "));

         foreach($answers as $answer)
                {

            $questionDetNew = '';
            $questionDetOld = DB::select(DB::raw("select * from questions where  id='$answer->question_id' "));
             if(is_array($questionDetOld))
                echo "questionDetOld true";

            foreach ($questionDetOld as $question) {
                # code...
                echo "<br/>";
                echo $question->id;
                echo $question->question_text;
                echo $user->event; echo"<br/>";


                $questionDetNew= DB::select(DB::raw("select * from questions where question_text='$question->question_text' and event='$user->event' and question_group='$question->question_group' "));

            }

            if(is_array($questionDetNew)){
            foreach ($questionDetNew as $questionx) {
                # code..
                if($questionx->id!=NULL){
                            echo "Updated<br/>";
            //DB::select(DB::raw("update answers set question_id='$questionx->id' where id='$answer->id' "));
        }
        else {
                echo "Its already existing ";
        }

            }
        }
     }
   }

}

更新

根据提供的评论，我想出了以下内容，但执行时间仍然没有太大差异

ini_set('max_execution_time', 800);
        $users= DB::select(DB::raw("select * from users where role!='admin_dd' and role!='sales_dd' "));
        // $users = User::where('role','!=','admin_dd')->orWhere('role','!=','sales_dd')->orderby('name')->get();
        $answers= DB::select(DB::raw("select * from answers "));
        $questionDetOld = DB::select(DB::raw("select * from questions"));

        foreach($users as $user)
        {
            foreach ($answers as $answer) {

                if($answer->user_id==$user->id)
                {
                    foreach($questionDetOld as $question)
                    {
                        if($question->id==$answer->question_id  )
                        {

                                    echo "<br/>";
                                    echo $question->id;
                                    echo $question->question_text;
                                    echo $user->event; echo"<br/>";
                                    //$questionDetNew= DB::select(DB::raw("select * from questions where question_text='$question->question_text' and event='$user->event' and question_group='$question->question_group' "));

                                    foreach ($questionDetOld as $questionx) {
                                        if($questionx->question_text==$question->question_text && $questionx->event==$user->event && $questionx->question_group==$question->question_group)
                                        {
                                          echo "Updated<br/>";
                                        }
                                    }
                        }
                    }
                }
            }
        }

最佳答案

首先，你在数据库中有正确的索引吗？ 其次，循环内有太多循环。在大量数据上，它可能非常慢。您可以通过准备由关键字段索引的数组来优化此脚本。这是一个例子:

    ini_set('max_execution_time', 800);

    $userIds = array();
    $users= DB::select(DB::raw("select * from users where role!='admin_dd' and role!='sales_dd' "));
    forach ($users as $user) {
        $userIds[] = $user->id;
    }

    $questionIds = array();
    $answers_tmp= DB::select(DB::raw("select * from answers where user_id in (".implode(',',$userIds).')'));
    foreach ($answers_tmp as $answer) {
        $answers[$answer->user_id][] = $answer;
        $questionIds[] = $answer->question_id;
    }

    $questionDetOld_tmp = DB::select(DB::raw("select * from questions where id in (".implode(',',$questionIds).')'));
    foreach ($questionDetOld_tmp as $question) {
        $questionDetOld[$question->id][] = $question;
    }

    foreach($users as $user)
    {
        if (isset($answers[$user->id]))
        {
            foreach ($answers[$user->id] as $answer)
            {
                if (isset($questionDetOld[$answer->question_id]))
                {
                    foreach($questionDetOld[$answer->question_id] as $question)
                    {
                        echo "<br/>";
                        echo $question->id;
                        echo $question->question_text;
                        echo $user->event; echo"<br/>";
                        //$questionDetNew= DB::select(DB::raw("select * from questions where question_text='$question->question_text' and event='$user->event' and question_group='$question->question_group' "));

                        foreach ($questionDetOld_tmp as $questionx)
                        {
                            if($questionx->question_text==$question->question_text && $questionx->event==$user->event && $questionx->question_group==$question->question_group)
                            {
                                echo "Updated<br/>";
                            }
                        }
                    }
                }
            }
        }
    }

关于php - 我的代码执行完成花费了太多时间，我们在Stack Overflow上找到一个类似的问题： https://stackoverflow.com/questions/32494660/