php - 搜索功能带有2年数据并加入

Question

这里是查询

SELECT *
FROM Table1
WHERE complete='Y'
  AND shipped='Y'
  AND active='Y'
  AND create_dttm > '2013-10-10 08:28:41'
  AND order_id IN
    (SELECT DISTINCT t1.order_id
     FROM Table2 t1
     INNER JOIN table3 t2 ON t1.prod_id = t2.prod_id
     WHERE t2.prod_sku LIKE '%D-600%'
       AND t1.create_dttm > '2013-02-15 08:28:41')

Answer 1

您在 WHERE 子句中使用子查询，这可能是查询执行缓慢的主要原因。尝试使用 JOINS 而不是子查询。

SELECT t1.*
FROM Table1 t1
INNER JOIN Table2 t2 ON T1.order_id = T2.order_id  
                        AND t2.create_dttm > '2013-02-15 08:28:41'
INNER JOIN table3 t3 ON t2.prod_id = t3.prod_id 
                         AND  t3.prod_sku LIKE '%D-600%'          
WHERE complete='Y'
  AND shipped='Y'
  AND active='Y'
  AND create_dttm > '2013-10-10 08:28:41'

还要检查表上的索引。