SQL查询优化实践

Question

我想实践我在小型数据库SQL查询优化方面的技能。我已经创建了一个非常简单的数据库：

​

我用数以千计的条目填充了它，并且需要一些复杂、愚蠢和未优化的查询来进行一些测试和练习我的优化技能。现在，我尝试编写一些查询，但它们对数据库没有太大影响。

我应该使用哪些关键词？有人能帮帮忙吗？

Answer 1

我发现很难想出真正糟糕的查询:-)这里有一些想法。希望其他人能想出更多。我建议您将表(空的，甚至包含一些样例行)放在https://dbfiddle.uk/中，我们可以在那里尝试我们的查询。例如，我不知道在下面的查询中是否没有任何语法错误。

查找名为xyz的用户拥有或评论的所有视频：

select distinct v.*
from videos v
left join user_have_videos uv on uv.video_id = v.video_id
left join users u1 on u1.user_id = uv.user_id
left join comments c on c.video_id = v.video_id
left join users u2 on u2.user_id = c.user_id
where u1.user_name = :user_name
   or u2.user_name = :user_name;

查找拥有至少三个不同视频的所有用户：

select distinct u.*
from users u
left join user_have_videos uv1 on uv1.user_id = u.user_id
left join videos v1 on v1.video_id = uv1.video_id
left join user_have_videos uv2 on uv2.user_id = u.user_id
left join videos v2 on v2.video_id = uv2.video_id
left join user_have_videos uv3 on uv3.user_id = u.user_id
left join videos v3 on v3.video_id = uv3.video_id
where v1.video_id <> v2.video_id
  and v1.video_id <> v3.video_id
  and v2.video_id <> v1.video_id
  and v2.video_id <> v3.video_id
  and v3.video_id <> v1.video_id
  and v3.video_id <> v2.video_id;

查找所有评论John Wayne的用户：

select distinct u.*
from users u
join comments c on c.user_id = u.user_id
where lower(comments) like '%john wayne%';

在这里，你会发现这是什么，并让它变得更好：

with cte(user_id, video_id, num) as
(
  select u.user_id, v.video_id, 1
  from videos v
  join user_have_videos uv on uv.video_id = v.video_id
  join users u on u.user_id = uv.user_id
  union all
  select u.user_id, min(v.video_id), min(cte.num) + 1
  from videos v
  join user_have_videos uv on uv.video_id = v.video_id
  join users u on u.user_id = uv.user_id
  join cte on cte.user_id = u.user_id 
           and cte.video_id < v.video_id
  group by u.user_id
)
select distinct u.*
from users u
join cte on cte.user_id = u.user_id and cte.num >= 10;