mysql - 表达此 SQL 查询的更好/替代方式

Question

写出以下查询的更好/更有效的方法是什么？

当前的 SQL 查询可以正常工作，但我担心随着数据库变大，处理时间会变长。

SELECT *
FROM room
JOIN catering ON room.room_id = catering.room_id
WHERE capacity BETWEEN '$minCapacity' AND '$maxCapacity' 
AND room.room_id NOT IN 
    (SELECT room_id
    FROM room_booking
    WHERE date_booked = '$us_date')
AND catering.grade = '$grade' ORDER BY room.capacity

Answer 1

您可以将 NOT IN 更改为 NOT EXISTS。如果有大量数据可以提高性能:

SELECT *
FROM room
JOIN catering ON room.room_id = catering.room_id
WHERE capacity BETWEEN '$minCapacity' AND '$maxCapacity' 
AND NOT EXISTS
(
    SELECT
        NULL
    FROM
        room_booking
    WHERE
        date_booked = '$us_date'
        AND room.room_id=room_booking.room_id
)
AND catering.grade = '$grade' 
ORDER BY room.capacity