Question

运行下面的查询需要永久时间。所有表格都包含超过200,000个记录。在合并语句中的所有列上都存在索引:

Select files.filename,
coalesce(temp_decmetadata.glare_roadGlare, 0) as glare_roadGlare,
coalesce(temp_decmetadata.glare_sun, 0) as glare_sun,
coalesce(temp_decmetadata.spray_heavySpray, 0) as spray_heavySpray,
coalesce(temp_kafaspdobjectdata.contrast_wellVisible, 0) as contrast_wellVisible,
coalesce(temp_kafaspdobjectdata.roadSafety_zebraCrossing, 0) as roadSafety_zebraCrossing,
coalesce(temp_visionmetadata.roadUser_bicycle, 0) as roadUser_bicycle,
coalesce(temp_visionmetadata.roadUser_car, 0) as roadUser_car,
coalesce(temp_visionmetadata.roadUser_pedestrian, 0) as roadUser_pedestrian
from files
left join 
(
 Select id,
 count(case when glare like  %roadGlare%  then 1 end) as glare_roadGlare,
 count(case when glare like  %sun%  then 1 end) as glare_sun,
 count(case when spray like  %heavySpray%  then 1 end) as spray_heavySpray
 from decmetadata
 group by id 
) temp_decmetadata on temp_decmetadata.id = files.id 
left join 
(
 Select id,
 count(case when contrast like  %wellVisible%  then 1 end) as contrast_wellVisible,
 count(case when roadSafety like  %zebraCrossing%  then 1 end) as roadSafety_zebraCrossing
 from kafaspdobjectdata
 group by id 
) temp_kafaspdobjectdata on temp_kafaspdobjectdata.id = files.id 
left join 
(
 Select id,
 count(case when roadUser like  %bicycle%  then 1 end) as roadUser_bicycle,
 count(case when roadUser like  %car%  then 1 end) as roadUser_car,
 count(case when roadUser like  %pedestrian%  then 1 end) as roadUser_pedestrian
 from visionmetadata
 group by id 
) temp_visionmetadata on temp_visionmetadata.id = files.id

"解释选择..."的结果附于下文:

"https://i.stack.imgur.com/7psPd.png" alt="解释选择.../"

Answer 1

我不是 MySQL 专家,但您可以将 JOIN 条件移动到 < code> WHERE 内部条件,并将行过滤到只满足任何 < code> CASE 条件的行,这样可以减少必须用 < code> LIKE 运算符评估的行数(这是本查询中最昂贵的操作)。以下脚本应该给您一个想法 :

...
CROSS JOIN  (
  SELECT
  SUM(CASE WHEN glare LIKE  %roadGlare%  THEN 1 ELSE 0 END) AS glare_roadGlare,
  SUM(CASE WHEN glare LIKE  %sun%  THEN 1 ELSE 0 END) AS glare_sun,
  SUM(CASE WHEN spray LIKE  %heavySpray%  THEN 1 ELSE 0 END) AS spray_heavySpray
  FROM decmetadata
  WHERE ( id = files.id ) -- benefits from an index over DECMETADATA.ID
    AND ( 1=2
      OR glare LIKE  %roadGlare% 
      OR glare LIKE  %sun% 
      OR spray LIKE  %heavySpray% 
    )
) temp_decmetadata
...

Answer 2

您的查询结构合理。问题是所有 like%xx% x% 查询。当您在子字符串匹配时, 索引对这些条款没有帮助。

尝试使用"http://dev.mysql.com/doc/refman/5.0/en/fulltext-search.html" rel=“nofollow”>全文搜索。

友情链接