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

WHERE IN slow if the IN List long

08150815 EntrantLegacy User Role Novice
Hello,

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.