mysql - Laravel5.1 查询和子查询

Question

如何改进此查询:

SELECT * FROM forum_thread WHERE
    thread_id not in (
        SELECT auth_id FROM forum_auth WHERE auth_group_id=1 and auth_type=2 and auth_visible=0
    )
    and thread_id in (
            SELECT thread_id FROM forum_thread WHERE
            category_id NOT IN (
                    SELECT auth_id FROM forum_auth WHERE auth_group_id=1 and auth_visible=0 and auth_type=1
            )
    )
ORDER BY last_post_id DESC limit 30

感谢您的回答

Answer 1

您可以尝试此查询来获得更好的性能:

SELECT * FROM forum_thread WHERE
thread_id NOT EXIST(
    SELECT auth_id FROM forum_auth WHERE auth_group_id=1 and auth_type=2 and auth_visible=0
)
and thread_id EXIST(
        SELECT thread_id FROM forum_thread WHERE
        category_id NOT EXIST(
                SELECT auth_id FROM forum_auth WHERE auth_group_id=1 and auth_visible=0 and auth_type=1
        )
)
ORDER BY last_post_id DESC limit 30

只需将NOT IN更改为NOT EXIST - 请参阅此链接:NOT IN vs NOT EXISTS

并将 IN 更改为 EXIST - 另请参阅此链接:Difference between EXISTS and IN in SQL?

享受吧!