Optimizing mysql IN query having large values

Question

I have a mysql query like

 SELECT `tbl_ticket`.`id`, `tbl_ticket`.`hd_user_username`, 
`tbl_ticket`.`hd_user_email`, `tbl_ticket`.`ticket_title`, 
`tbl_complain_type`.`complains` FROM `tbl_ticket` LEFT JOIN 
`tbl_ticket_complain` ON tbl_ticket_complain.ticket_id=tbl_ticket.id 
LEFT JOIN `tbl_complain_type` ON tbl_complain_type.id=tbl_ticket_complain.complain_id 
LEFT JOIN `tbl_assignment` ON tbl_assignment.ticket_id=tbl_ticket.id 
WHERE ((((`hd_user_username` LIKE '%searchterm%') 
AND (`tbl_assignment`.`id` IN ($array))) 
OR (`hd_user_email`='searchterm')) 
OR (`ticket_title`='searchterm')) OR (`tbl_complain_type`.`complains`='searchterm')

$array contains around 7000 values like `$array=array(1,2,3,..)`

This query takes around 8 seconds to execute. Is there any alternative solution for this query ? The value of $array is got from another query

select max(id) from tbl_assignment group by ticket_id

The slowness of query is due to multiple joins between tables

Answer 1

If the values in the array use in you IN clause come from a select you could use the fact that
An IN clause is equivalent to an inner join so you could use a inner join between your_table_with_id and the table.column you need for match eg:

  SELECT `
      tbl_ticket`.`id`
      , `tbl_ticket`.`hd_user_username`
      , `tbl_ticket`.`hd_user_email`
      , `tbl_ticket`.`ticket_title`
      , `tbl_complain_type`.`complains` 
  FROM `tbl_ticket` 
  LEFT JOIN `tbl_ticket_complain` ON tbl_ticket_complain.ticket_id=tbl_ticket.id 
  LEFT JOIN `tbl_complain_type` ON tbl_complain_type.id=tbl_ticket_complain.complain_id 
  LEFT JOIN `tbl_assignment` ON tbl_assignment.ticket_id=tbl_ticket.id 
  INNER JOIN your_table_with_id ON `tbl_assignment`.`id` = JOIN your_table_with_id.id
  WHERE ((((`hd_user_username` LIKE '%searchterm%') 
  OR (`hd_user_email`='searchterm')) 
  OR (`ticket_title`='searchterm')) OR (`tbl_complain_type`.`complains`='searchterm')

Remeber also that the content of values use IN clause is limited and fail when the limit is exceeded

and in your case

  SELECT `
      tbl_ticket`.`id`
      , `tbl_ticket`.`hd_user_username`
      , `tbl_ticket`.`hd_user_email`
      , `tbl_ticket`.`ticket_title`
      , `tbl_complain_type`.`complains` 
  FROM `tbl_ticket` 
  LEFT JOIN `tbl_ticket_complain` ON tbl_ticket_complain.ticket_id=tbl_ticket.id 
  LEFT JOIN `tbl_complain_type` ON tbl_complain_type.id=tbl_ticket_complain.complain_id 
  LEFT JOIN `tbl_assignment` ON tbl_assignment.ticket_id=tbl_ticket.id 
  INNER JOIN (
    select max(id) as id
    from tbl_assignment 
    group by ticket_id
  ) t ON `tbl_assignment`.`id` = t.id
  WHERE ((((`hd_user_username` LIKE '%searchterm%') 
  OR (`hd_user_email`='searchterm')) 
  OR (`ticket_title`='searchterm')) OR (`tbl_complain_type`.`complains`='searchterm'))