php - 如何优化此查询以生成许多逗号分隔的数据？

Question

这是我的查询:

SELECT
   t1.vehicle_id vehicleId,
   GROUP_CONCAT(distinct(t2.vehicle_name)) vehiclename,
   t1.from_state_id fromStateId,
   GROUP_CONCAT(distinct(t3.state_name)) fromState,
   t1.to_state_id toStateId,
   GROUP_CONCAT(distinct(t4.state_name)) toState,
   t1.from_city_id fromCityId,
   GROUP_CONCAT(distinct(t5.city_name)) fromCity,
   t1.to_city_id toCitiesId,
   GROUP_CONCAT(distinct(t6.city_name)) toCities 
FROM
   tbl_vendor_workstations as t1 
   LEFT JOIN
      tbl_vehicles as t2 
      ON find_in_set(t2.id, t1.vehicle_id) 
   Left JOIN
      tbl_states as t3 
      ON find_in_set(t3.id, t1.from_state_id) 
   Left JOIN
      tbl_states as t4 
      ON find_in_set(t4.id, t1.to_state_id) 
   Left JOIN
      tbl_city as t5 
      ON find_in_set(t5.id, t1.from_city_id) 
   Left JOIN
      tbl_city as t6 
      ON find_in_set(t6.id, t1.to_city_id) 
where
group by
   t1.id ";

当我执行此查询时，它运行速度非常慢。是否存在找到许多彗差单独数据的替代方法？

Answer 1

您似乎在 tbl_vendor_workstations 中有一些字段，其中包含以逗号分隔的 id 列表。这是一个非常糟糕的主意。它会阻止索引的有效使用，并且还意味着您对每个项目保存的值列表有限制(即，取决于您存储它们的字段的长度)。

我建议您更改数据库以使用链接表。其中每个都将存储来自 tbl_vendor_workstations 的行 id 和(例如)vehicle_id，每个组合 1 行。因此，一个 tbl_vendor_workstations.id 可能在此表上有数十行。然后，您从 tbl_vendor_workstations 加入链接表，然后加入 tbl_vehicles 以获取该车辆的详细信息。

CREATE TABLE tbl_vehicles_link
(
    vendor_workstations_id  INT(11),
    vehicle_id  INT(11),
    PRIMARY KEY (`vendor_workstations_id`, `vehicle_id`),
    KEY `vehicle_id` (`vehicle_id`),
);

那么你的 SQL 就会是这样的

SELECT t1.vehicle_id vehicleId,
           GROUP_CONCAT(distinct(t2.vehicle_name)) vehiclename,
           t1.from_state_id fromStateId,
           GROUP_CONCAT(distinct(t3.state_name)) fromState,
           t1.to_state_id toStateId,
           GROUP_CONCAT(distinct(t4.state_name)) toState,
           t1.from_city_id fromCityId,
           GROUP_CONCAT(distinct(t5.city_name)) fromCity,
           t1.to_city_id toCitiesId,
           GROUP_CONCAT(distinct(t6.city_name)) toCities 
FROM tbl_vendor_workstations as t1 
LEFT OUTER JOIN tbl_vehicles_link as tl2    ON tl2.vendor_workstations_id = t1.id
LEFT OUTER JOIN tbl_vehicles as t2  ON t2.id = tl2.vehicle_id
LEFT OUTER JOIN tbl_states as tl3   ON tl3.vendor_workstations_id = t1.id
LEFT OUTER JOIN tbl_states as t3    ON t3.id = t1.from_state_id
LEFT OUTER JOIN tbl_states as tl4   ON tl4.vendor_workstations_id = t1.id
LEFT OUTER JOIN tbl_states as t4    ON t4.id = t1.to_state_id
LEFT OUTER JOIN tbl_city as tl5     ON tl5.vendor_workstations_id = t1.id
LEFT OUTER JOIN tbl_city as t5      ON t5.id = t1.from_city_id
LEFT OUTER JOIN tbl_city as tl6     ON tl6.vendor_workstations_id = t1.id
LEFT OUTER JOIN tbl_city as t6      ON t6.id = t1.to_city_id
WHERE t1.vendor_id=300 AND enterprise_user_id=68 
GROUP BY t1.id