Not the answer you need?
Register and ask your own question!

WHERE IN slow when the IN list is long

08150815 EntrantLegacy User Role Novice

below is my query. It is very slow and I wonder if there is anything that makes it faster.
This is generated by a forum software and is not or only conditionally changeable.

Does anyone have a helpful idea?

The tables have the following number of records.
xf_forum: ~4000 xf_node: ~4000 xf_user: ~442.406 xf_thread: ~598.012 xf_forum_read: 9.342 xf_thread_read: 286 Unfortunately I can't specify the whole query! There are about 3000 IDs
SELECT `xf_forum`.*, `xf_node_Node_1`.*, `xf_user_LastPostUser_2`.*, `xf_thread_LastThread_3`.*, `xf_forum_read_Read_4`.*, `xf_thread_read_Read_5`.* 
 FROM `xf_forum` LEFT JOIN `xf_node` AS `xf_node_Node_1` ON (`xf_node_Node_1`.`node_id` = `xf_forum`.`node_id`) LEFT JOIN `xf_user` AS `xf_user_LastPostUser_2` ON (`xf_user_LastPostUser_2`.`user_id` = `xf_forum`.`last_post_user_id`) LEFT JOIN `xf_thread` AS `xf_thread_LastThread_3` ON (`xf_thread_LastThread_3`.`thread_id` = `xf_forum`.`last_thread_id`) LEFT JOIN `xf_forum_read` AS `xf_forum_read_Read_4` ON (`xf_forum_read_Read_4`.`node_id` = `xf_forum`.`node_id` AND `xf_forum_read_Read_4`.`user_id` = '90052') LEFT JOIN `xf_thread_read` AS `xf_thread_read_Read_5` ON (`xf_thread_read_Read_5`.`thread_id` = `xf_thread_LastThread_3`.`thread_id` AND `xf_thread_read_Read_5`.`user_id` = '90052') WHERE (`xf_forum`.`node_id` IN (2249, 230, 63, 326, 372, 93, 2032, 2976, 96, 687, ......))
Sign In or Register to comment.

MySQL, InnoDB, MariaDB and MongoDB are trademarks of their respective owners.
Copyright ©2005 - 2020 Percona LLC. All rights reserved.