优化嵌套的SELECT查询，并使用ActiveRecord查询应用编程接口进行优化

Question

我有这个SQL查询来选择我的一些优惠，它可以像预期的那样工作。

SELECT * FROM `offers` WHERE region='26'

AND EXISTS (
SELECT * FROM `modificator_values` WHERE
    `modificator_values`.`offer_id`=`offers`.`id` AND
     (modificator_values.modificator_id = "5") AND (modificator_values.data = "data1")
) 

AND EXISTS (
SELECT * FROM `modificator_values` WHERE
     `modificator_values`.`offer_id`=`offers`.`id` AND
     (modificator_values.modificator_id = "8") AND (modificator_values.data = "data2")
)

Answer 1

虽然不使用任何API，但我建议您使用此技巧，您只需检查两个子查询中是否存在，并可以通过执行以下操作来节省“双倍”时间：

SELECT * FROM offers 
WHERE region='26'

AND (SELECT COUNT(*)
  FROM (
    SELECT modificator_id,COUNT(data) 
    FROM modificator_values 
    WHERE
      modificator_values.offer_id=1 AND
       (((modificator_values.modificator_id = "5") AND (modificator_values.data = "data1")) OR
       ((modificator_values.modificator_id = "8") AND (modificator_values.data = "data2")))
    GROUP BY modificator_id 
    ) s
    ) > 1

我已经在这个模式上进行了测试：

CREATE TABLE offers 
    (
     id int auto_increment primary key, 
     region varchar(20)
    );
CREATE TABLE modificator_values 
    (
     modificator_id int auto_increment primary key, 
     data varchar(20), 
     offer_id int
    );

INSERT INTO offers VALUES (1,'26');
INSERT INTO modificator_values VALUES(5,'data1',1);
INSERT INTO modificator_values VALUES(8,'data2',1);