如何提高子查询性能？ [英] How to improve the sub-query performance?

问题描述

我有2张桌子，个用户和个游戏。用户可以有很多游戏。我需要所有拥有个游戏的个用户，以及他们的 games_hosted 计数（个游戏列已完成的 为 true 。

I have 2 tables, users and games. User can have many games. I need all the users who have games, along with their games_hosted count (games that have column finished to true.

PS我需要将所有数据加载到管理表中。由于游戏太多，我决定对数据进行分页和限制，但即使这样也限制了以下内容查询需要花费相同的时间。如何更好地查询？

P.S. I need all the data to load in a Admin Table. As there are too many games. I decided to paginate and limit data. But even limiting also the following query is taking the same time. How can I query this in a better way?

 select 
  distinct "uid", 
  "username", 
  (
    select 
      count(id) 
    from 
      games 
    where 
      state = 'finished' 
      and user_uid = users.uid
  ) as games_hosted 
from 
  "users" 
  inner join "games" on "games"."user_uid" = "users"."uid" 
where 
  "games"."state" in ('published', 'finished') 
  and "username" < 'HariShankar' 
order by 
  "username" desc 
limit 
  10

推荐答案

您可以尝试以下操作-使用情况，当表达式

You can try below- using case when expression

select "uid", "username", count(case when state = 'finished' then id end) as games_hosted 
from "users" inner join "games" 
on "games"."user_uid" = "users"."uid" 
where "games"."state" in ('published', 'finished') and "username" < 'HariShankar' 
group by "uid", "username"
order by "username" desc 
limit 10

这篇关于如何提高子查询性能？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！