Saqueib Saqueib - 21 days ago 11
MySQL Question

Codeigniter The SELECT would examine more than MAX_JOIN_SIZE rows

I have developed a Attendance management system, i want to generate report by running following query, it will be a large number of rows, i am on shared host and getting below error :

Query:

SELECT
`attendance`.*, `users`.`id` AS user_id,
CONCAT_WS(" ", `user_profiles`.`fname`, `user_profiles`.`lname` ) AS name,
`user_department`.`did`, `user_department`.`dep_name` FROM (`attendance`)
LEFT JOIN `users` ON `users`.`username` = `attendance`.`emp_code`
LEFT JOIN `user_profiles` ON `user_profiles`.`user_id` = `users`.`id`
LEFT JOIN `in_department` ON `in_department`.`user_id` = `user_profiles`.`user_id`
LEFT JOIN `user_department` ON `in_department`.`dep_id` = `user_department`.`did`
WHERE `banned` = '0'
AND `for_date` >= '2012-05-28'
AND `for_date` <= '2012-06-28'
ORDER BY `emp_code` asc, `for_date` asc


Error:

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

How I can run this query using active record in codeigniter
SET SQL_BIG_SELECTS=1

Please help, do i need to index my table

Answer

You can simply issue a query containing the SET command:

$this->db->query('SET SQL_BIG_SELECTS=1'); 
Comments