MySQL 查询卡住

Question

我有一个以下查询

    SELECT DISTINCT
    Products.Product_name
    FROM Order_details
    INNER JOIN Products
    ON Products.Product_codenumber = Order_details.Product_codenumber
    INNER JOIN Orders
    ON Order_details.Order_ID = Orders.Order_ID
    INNER JOIN Customers
    ON Orders.Customer_ID = Customers.Customer_ID
    INNER JOIN Discount_cards
    ON Discount_cards.Phone_number = Customers.Login
    WHERE Orders.Status_ID = '5'
    AND (DATE(Orders.Time) BETWEEN '$start_date' AND '$end_date')

旨在从订单信息中获取折扣卡用户订购的系列或产品。当我第一次写它时，它似乎工作正常，但最近我被告知它卡住了，我不知道为什么。我试着玩了一下，似乎当我添加日期范围，甚至只是单个日期搜索条件时，它会卡住(可以永远在 PHPMyAdmin 中显示“正在加载”弹出窗口)；除此之外，执行大约需要 1 秒。

Here's what EXLAIN gives me.

Answer 1

好吧，显然丢弃 Discount_cards 的内部联接并用 WHERE IN 替换它可以解决此问题:

    SELECT DISTINCT
    Products.Product_name
    FROM Order_details
    INNER JOIN Products
    ON Products.Product_codenumber = Order_details.Product_codenumber
    INNER JOIN Orders
    ON Order_details.Order_ID = Orders.Order_ID
    INNER JOIN Customers
    ON Orders.Customer_ID = Customers.Customer_ID
    WHERE 
    Customers.Login IN (
        SELECT
        Phone_number
        FROM Discount_cards
        )
    AND Orders.Status_ID = '5'
    AND (DATE(Orders.Time) BETWEEN '$start_date' AND '$end_date')

我想知道为什么会发生卡住，我们将不胜感激。