This database has worked fine for two years, now it's getting the following error? Can someone explain this issue and suggest a way to resolve this?

Error Number: 1104

The SELECT would examine more than MAX_JOIN_SIZE rows; check your WHERE and use SET SQL_BIG_SELECTS=1 or SET MAX_JOIN_SIZE=# if the SELECT is okay

Code:

select 
    salary_tb.*, order_tb.order_no, 
    daily_target_tb.customer as customer_name, 
    jobs_tb.job as job_name 
from 
    salary_tb 
left join 
    order_tb on salary_tb.order_id = order_tb.id 
left join 
    daily_target_tb on order_tb.id = daily_target_tb.order_id 
left join 
    jobs_tb on salary_tb.job_id = jobs_tb.id 
where 
    salary_tb.id > 0 
    and salary_tb.isDeleted = 0 
    and salary_tb.employee_id = '1' 
group by 
    salary_tb.id

Filename: models/SalaryModel.php

Line Number: 22

CodePudding user response：

The issue is your query is either returning huge number of rows which is more than value set for MAX_JOIN_SIZE configuration or your SQL_BIG_SELECTS is set to 0 which will not allow you to run queries which take too long to return a result. You can do one/all of the following 3 things :

1.SET SQL_BIG_SELECTS=1;

2.MAX_JOIN_SIZE= (More than the no of rows returned by the join statement)

3. Refine your query so that it returns appropriate values within the set parameters and in less time.(Using Indexing, Returning less no. of rows).

CodePudding user response：

Current Query

function get_rows($where)
{
    $sql =      
    "select salary_tb.*, order_tb.order_no, daily_target_tb.customer as customer_name, jobs_tb.job as job_name
            from salary_tb
            left join order_tb on salary_tb.order_id = order_tb.id
            left join daily_target_tb on order_tb.id = daily_target_tb.order_id
            left join jobs_tb on salary_tb.job_id = jobs_tb.id
            where salary_tb.id > 0";
            
    if($where != ""){
        $sql .= $where;
    }

    
    $query =# $this->db->query('SET SQL_BIG_SELECTS=1'); 
            $this->db->query($sql);
    return $query->result_array();
}