如何更快地检索 500k 记录数据库数据？

用户7325973

我有两个表 T1 1 000 条记录和 T2 500 000 条记录。我有一个查询，我在其中运行它们之间的连接并通过执行一些聚合来获取数据。我的页面似乎加载缓慢。是否有任何方法可以使此查询更快？

我在正在执行聚合的列上创建了索引。我认为这是一个笼统的说法。

      $query = Mymodel::selectRaw("supplier_data.name as distributor,supplier_data.name as name, supplier_data.group_id as group_id, supplier_data.pay,supplier_data.group_id as submitted_group_plan,supplier_data.group_id as group_id_string,
            (SELECT sum(t.net_claim) AS trans_number 
            FROM transactions_data_new as t 
            JOIN  `supplier_data` AS d ON  `t`.`member_id` =  `d`.`group_id`
            WHERE
            (
                (
                t.`submit_date`>= '$date_from' and t.`submit_date`<= '$date_to' 
                AND t.`member_id` = supplier_data.group_id
                )
                OR
                (
                    (t.claim_status  IS NULL)
                    AND
                    (t.submit_date is NULL)
                )
            )
            AND d.id = supplier_data.id
        ) as trans_number,


        (SELECT sum(t.claim) AS trans_number 
            FROM transactions_data_new as t 
            JOIN  `supplier_data` AS d ON  `t`.`member_id` =  `d`.`group_id`
            WHERE
            (
                (
                t.`submit_date`>= '$date_from' and t.`submit_date`<= '$date_to' 
                AND t.`member_id` = supplier_data.group_id
                )
                OR
                (
                    (t.claim_status  IS NULL)
                    AND
                    (t.submit_date is NULL)
                )
            )
            AND d.id = supplier_data.id
        ) as claim,

        (SELECT sum(t.reversed) AS trans_number 
            FROM transactions_data_new as t 
            JOIN  `supplier_data` AS d ON  `t`.`member_id` =  `d`.`group_id`
            WHERE
            (
                (
                t.`submit_date`>= '$date_from' and t.`submit_date`<= '$date_to' 
                AND t.`member_id` = supplier_data.group_id
                )
                OR
                (
                    (t.claim_status  IS NULL)
                    AND
                    (t.submit_date is NULL)
                )
            )
            AND d.id = supplier_data.id
        ) as reversed,

        (SELECT sum(t.reversal) AS trans_number 
            FROM transactions_data_new as t 
            JOIN  `supplier_data` AS d ON  `t`.`member_id` =  `d`.`group_id`
            WHERE
            (
                (
                t.`submit_date`>= '$date_from' and t.`submit_date`<= '$date_to'
                AND t.`member_id` = supplier_data.group_id
                )
                OR
                (
                    (t.claim_status  IS NULL)
                    AND
                    (t.submit_date is NULL)
                )
            )
            AND d.id = supplier_data.id
        ) as reversal
            ");

M哈立德朱奈德

我认为不需要使用相同的子句和对同一表的多个子选择过于复杂/重复，这可以使用单个左连接完成

SELECT 
  s.name AS distributor,
  s.name AS name,
  s.group_id AS group_id,
  s.pay,
  s.group_id AS submitted_group_plan,
  s.group_id AS group_id_string,
  SUM(t.net_claim) AS trans_number,
  SUM(t.claim) AS claim,
  SUM(t.reversed) reversed,
  SUM(t.reversal) reversal 
FROM
  supplier_data s 
  LEFT JOIN transactions_data_new t 
    ON `t`.`member_id` = s.`group_id` 
    AND (
      (
        t.`submit_date` >= '$date_from' 
        AND t.`submit_date` <= '$date_to'
      ) 
      OR (
        t.claim_status IS NULL 
        AND t.submit_date IS NULL
      )
    ) 
GROUP BY s.name,
  s.group_id,
  s.pay

本文收集自互联网，转载请注明来源。

如有侵权，请联系 [email protected] 删除。