Codeigniter 3 处理大量数据的更好方式

问：

function user_get_datatables($doctype, $accountCodesList, $role, $length, $start, $draw, $search, $order, $orderColumn, $orderDirection){
    ini_set('memory_limit', '1024M');
    try{
        

        $this->db->select('documents.id','documents.date_of_production','documents.account_code', 
        'documents.name','documents.policy_number','documents.doctype','documents.status','documents.file_link','documents.created_on','documents.updated_on')->from($this->documents_table);

        if($accountCodesList!==false){
            $this->db->group_start();
            foreach($accountCodesList as $item){
                $this->db->or_where('documents.account_code',$item);
            }
            $this->db->group_end();
        }

        if($doctype!==null || sizeof($doctype)>0){
            $this->db->group_start();
            foreach($doctype as $item){
                $this->db->or_where('documents.doctype',$item);
            }
            $this->db->group_end();
        }else{
            $this->db->where('documents.doctype',"false");
        }
        $this->db->where('documents.status',true);

        if($search != ""  && $search != null){
            $this->db->group_start();
            $this->db->or_like('documents.original_file_name', $search);
            $this->db->or_like('documents.account_code', $search);
            $this->db->or_like('documents.policy_number', $search);
            $this->db->group_end(); 
        }
        if($order != null){
            $this->db->order_by($this->user_column_order[$orderColumn], $orderDirection);
        } 
        else if(isset($this->order)){
            $order = $this->order;
            $this->db->order_by(key($order), $order[key($order)]);
        }

        // Apply LIMIT to the query
        if ($length != -1) {
            $this->db->limit($length, $start);
        }
        $query = $this->db->get();
        // echo $this->db->last_query();
        $res = $query->result();

        return array(
            'data' => $res,
            'isSuccessful' => true,
            'message' => "",
            'search' => $search,
            "length" =>  intval($length),
            "draw" => $draw,
            'start' => $start,
        );
    }
    catch(Exception $e){
        return array(
            'data' => array(),
            'isSuccessful' => false,
            'message' => 'An error occurred: ' . $e->getMessage(),
        );

    }
}

这是我从数据库（大约 800,000 行）中提取一些文档数据的功能。结果取决于 accountCodesList 的内容。accountCodesList 是一个字符串数组。问题是，当 accountCodesList 很小时，一切都很好。但是，我有一些用户拥有超过 8000 个帐户代码。这会导致 or_where 语句非常长，以至于超时并遇到内存不足错误。

我已经尝试过where_in但对于某些用户来说，由于它的长度，我无法使用它。

有没有办法加快速度？

我正在使用 Codeigniter 3

编辑：

我也尝试过创建临时表的方法，但需要 3 分钟以上，结果仍然没有出来：

    function user_get_datatables($doctype, $accountCodes, $role, $length, $start, $draw, $search, $order, $orderColumn, $orderDirection){
            ini_set('memory_limit', '1024M');
            try{
                $this->db->query("CREATE TEMPORARY TABLE temp_account_codes (account_code VARCHAR(255))");
                
                // Prepare the values for insertion
                $values = array_map(function($code) {
                    return "('$code')";
                }, $accountCodes);
                // Insert data into the temporary table using a multi-row insert
                $insertValues = implode(', ', $values);
                $this->db->query("INSERT INTO temp_account_codes (account_code) VALUES $insertValues");
    
                // Retrieve all rows from the temporary table
                $this->db->select('*')
                    ->from('temp_account_codes');
                $tempTableQuery = $this->db->get();
                $tempTableData = $tempTableQuery->result();
    
                echo json_encode($tempTableData);
    
                $this->db->select($this->user_column_order)
                ->from($this->documents_table)
                ->join('temp_account_codes', 'documents.account_code = temp_account_codes.account_code', 'inner');
    
    
                if($doctype!==null || sizeof($doctype)>0){
                    $this->db->group_start();
                    foreach($doctype as $item){
                        $this->db->or_where('documents.doctype',$item);
                    }
                    $this->db->group_end();
                }else{
                    $this->db->where('documents.doctype',"false");
                }
                $this->db->where('documents.status',true);
                // $i = 0;
    
                if($search != ""  && $search != null){
                    $this->db->group_start();
                    $this->db->or_like('documents.original_file_name', $search);
                    $this->db->or_like('documents.account_code', $search);
                    $this->db->or_like('documents.policy_number', $search);
                    $this->db->group_end(); 
                }
                if($order != null){
                    $this->db->order_by($this->user_column_order[$orderColumn], $orderDirection);
                } 
                else if(isset($this->order)){
                    $order = $this->order;
                    $this->db->order_by(key($order), $order[key($order)]);
                }
    
                // Apply LIMIT to the query
                if ($length != -1) {
                    $this->db->limit($length, $start);
                }
                $query = $this->db->get();
                echo $this->db->last_query();
                $res = $query->result();
    
                return array(
                    'data' => $res,
                    'isSuccessful' => true,
                    'message' => "",
                    'search' => $search,
                    "length" =>  intval($length),
                    "draw" => $draw,
                    'start' => $start,
                );
            }
            catch(Exception $e){
                return array(
                    'data' => array(),
                    'isSuccessful' => false,
                    'message' => 'An error occurred: ' . $e->getMessage(),
                );
    
            }
        }

EDIT 2:

I tried just joining the tables with each other but the speed is still slow when it comes to large amounts of account codes

function user_get_datatables($doctype, $userId, $role, $length, $start, $draw, $search, $order, $orderColumn, $orderDirection){
        ini_set('memory_limit', '1024M');
        try{

            $this->db->select($this->user_column_order)
            ->from($this->documents_table)
            ->join('account_codes', 'documents.account_code = account_codes.account_code', 'inner')
            ->join('user_account_codes', 'account_codes.id = user_account_codes.account_code_id', 'inner');

            $this->db->where('user_account_codes.user_id',$userId);

            if($doctype!==null || sizeof($doctype)>0){
                $this->db->group_start();
                foreach($doctype as $item){
                    $this->db->or_where('documents.doctype',$item);
                }
                $this->db->group_end();
            }else{
                $this->db->where('documents.doctype',"false");
            }
            $this->db->where('documents.status',true);
            // $i = 0;

            if($search != ""  && $search != null){
                $this->db->group_start();
                $this->db->or_like('documents.original_file_name', $search);
                $this->db->or_like('documents.account_code', $search);
                $this->db->or_like('documents.policy_number', $search);
                $this->db->group_end(); 
            }
            if($order != null){
                $this->db->order_by($this->user_column_order[$orderColumn], $orderDirection);
            } 
            else if(isset($this->order)){
                $order = $this->order;
                $this->db->order_by(key($order), $order[key($order)]);
            }

            // Apply LIMIT to the query
            if ($length != -1) {
                $this->db->limit($length, $start);
            }
            $query = $this->db->get();
            //echo $this->db->last_query();
            $res = $query->result();

            return array(
                'data' => $res,
                'isSuccessful' => true,
                'message' => "",
                'search' => $search,
                "length" =>  intval($length),
                "draw" => $draw,
                'start' => $start,
            );
        }
        catch(Exception $e){
            return array(
                'data' => array(),
                'isSuccessful' => false,
                'message' => 'An error occurred: ' . $e->getMessage(),
            );

        }
    }