优化选择每个组的最后一条记录的子查询 [英] Optimize sub-query selecting last record of each group

问题描述

我有这个查询，这是一个依赖查询，并执行很多时间

  SELECT 
 u.id， 
 u.user_name，
 ifnull（（从地图选择经度，其中user_id = u.id按map_id desc limit 1排序），0）as Longitude，
 ifnull（（从地图选择纬度user_id = u.id按照map_id desc limit 1），0）作为经度，
（选择从地图创建，其中user_id = 1 order by创建desc限制1）作为LatestTime 
 FROM用户作为u 
 WHERE id IN（SELECT 
 user1_id FROM relation 
 WHERE users.id = 1）
 ORDER BY id; 
  

我试过这个查询（依赖）

  SELECT 
 u.id，
 u.user_name，
 m.map_id，
 m.longitude，
 m。纬度，
 m。创建的日期
 FROM用户为u 
左连接（选择
 map_id，
经度，
纬度，
 user_id ，
最大（创建）作为`日期'
从映射
 group by user_id）作为m 
在m.user_id = u.id 
 WHERE id IN（SELECT 
 user1_id FROM关系
 WHERE users.id = 1）
 ORDER BY id; 
  

问题是第一个查询依赖并且工作正常，但执行时间很长。第二个查询的问题是它没有获取最新创建的时间。
现在我想优化这个查询。主题是在子查询中，我首先进行组合，然后我试图获取每组的最后一个记录。这里是表格结构。

  users：id，user_name 
 map：map_id，user_id，longitude，latitude，创建
关系：id，user1_id，user2_id，relation 
  

解决方案

在需要性能的情况下， SELECT 子句中的子查询的确是一种痛苦，并且必须被放逐：）

<您可以重写这部分：

  SELECT 
 u.id，
 u.user_name，
 ifnull（（从地图中选择经度，其中user_id = u.id按map_id desc limit 1排序），0）as Longitude，
 ifnull（（从地图选择纬度，其中user_id = u.id按map_id排序desc限制1），0）作为经度，
（选择从地图创建，其中user_id = 1的顺序由创建的desc限制1）为LatestTime 
 FROM用户作为u 
  
  
 
 
在： 
 
 
 选择
 u .id，
 u.user_name，
 COALESCE（m1.longitude，0）as lon gitude，
 COALESCE（m1.latitude，0）as latitude 
 FROM users u 
 LEFT JOIN map m1 ON m1.user_id = u.id 
 LEFT JOIN map m2 ON m2。 user_id = m1.user_id AND m2.map_id> m1.map_id 
 WHERE m2.map_id IS NULL 
  
我写了一个关于查询的简短说明结构在这个答案。这是一个非常好的技巧，因为它更具可读性，无需子查询且性能更好。
 
 
 
我没有看过 IN 部分，但是如果上面没有帮助的话。 
 
 
  Edit1：您可以提取创建日期并使用一个MAX（）来代替。 
 
 
  SELECT 
 u.id，
 u.user_name，
 COALESCE（m1.longitude，0）as longitude，
 COALESCE（m1.latitude，0）as latitude，
 created.LatestTime 
 FROM（SELECT MAX（created）FROM map WHERE user_id = 1）创建了
 INNER JOIN用户u ON TRUE 
 LEFT JOIN map m1 ON m1.user_id = u.id 
 LEFT JOIN map m2 ON m2.user_id = m1.user_id AND m2.map_id> ; m1.map_id 
 WHERE m2.map_id IS NULL 
  
 
I have this query which is a dependant query and taking much execution time
SELECT
  u.id,
  u.user_name,
  ifnull((select longitude from map where user_id = u.id order by map_id desc limit 1 ),0) as Longitude,
  ifnull((select latitude from map where user_id = u.id order by map_id desc limit 1 ),0) as Longitude,
  (select  created  from map  where user_id = 1  order by created desc   limit 1) as LatestTime
FROM users as u
WHERE id IN(SELECT
          user1_id FROM relation
        WHERE users.id = 1)
ORDER BY id;
I tried this query in (dependant)
SELECT
  u.id,
  u.user_name,
  m.map_id,
  m.longitude,
  m.latitude,
  m.Date as created
FROM users as u
  left join (select
           map_id,
           longitude,
           latitude,
           user_id,
           max(created) as `Date`
         from map
         group by user_id) as m
    on m.user_id = u.id
WHERE id IN(SELECT
          user1_id FROM relation
        WHERE users.id = 1)
ORDER BY id;
The problem is that the first query is dependent and working fine but taking much execution time. With the second query the problem is that it is not fetching the latest created time.
Now i want to optimise this query. The theme is that in subquery i am first making group then i am trying to get the last record of each group. and here is the tables structure.
users : id , user_name
map   : map_id ,  user_id ,longitude , latitude, created 
relations : id , user1_id , user2_id , relation

解决方案
Where performance is needed, subqueries in the SELECT clause are indeed a pain and have to be banished :)


You can rewrite this part:
SELECT
    u.id,
    u.user_name,
    ifnull((select longitude from map where user_id = u.id order by map_id desc limit 1 ),0) as Longitude,
    ifnull((select latitude from map where user_id = u.id order by map_id desc limit 1 ),0) as Longitude,
    (select  created  from map  where user_id = 1  order by created desc   limit 1) as LatestTime
FROM users as u
In:
SELECT
    u.id,
    u.user_name,
    COALESCE(m1.longitude, 0) as longitude,
    COALESCE(m1.latitude, 0) as latitude
FROM users u
LEFT JOIN map m1 ON m1.user_id = u.id
LEFT JOIN map m2 ON m2.user_id = m1.user_id AND m2.map_id > m1.map_id
WHERE m2.map_id IS NULL
I wrote a short explanation of the query structure in this answer. It's a really nice trick to learn as it is more readable, subquery-less and performance wiser.

I haven't looked at the IN part yet but will if the above didn't help.

Edit1: You can extract the created date and use a MAX() instead.
SELECT
    u.id,
    u.user_name,
    COALESCE(m1.longitude, 0) as longitude,
    COALESCE(m1.latitude, 0) as latitude,
    created.LatestTime
FROM (SELECT MAX(created) FROM map WHERE user_id = 1) created
INNER JOIN users u ON TRUE
LEFT JOIN map m1 ON m1.user_id = u.id
LEFT JOIN map m2 ON m2.user_id = m1.user_id AND m2.map_id > m1.map_id
WHERE m2.map_id IS NULL


                        
这篇关于优化选择每个组的最后一条记录的子查询的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！