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

问题描述

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

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

推荐答案

在需要性能的地方，SELECT 子句中的子查询确实很痛苦，必须摒弃:)

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

你可以重写这部分:

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

在:

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.

我还没有查看 IN 部分，但如果上述内容没有帮助，我会查看.

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

Edit1:您可以提取创建日期并改用 MAX().

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屋！