php - 优化具有大值的 mysql IN 查询

Question

我有一个像

这样的 mysql 查询

 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,..)`

执行此查询大约需要 8 秒。此查询是否有其他解决方案？$array 的值是从另一个查询中得到的

select max(id) from tbl_assignment group by ticket_id

查询慢是因为表间有多个join

Answer 1

如果在您的 IN 子句中使用的数组中的值来自一个选择，您可以使用这样的事实
IN 子句等同于内部联接，因此您可以在 your_table_with_id 和匹配所需的 table.column 之间使用内部联接，例如:

  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')

还要记住，values use IN 子句的内容是有限制的，超过限制就会失效

在你的情况下

  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'))