优化子查询选择每组的最后一条记录

本文介绍了优化子查询选择每组的最后一条记录的处理方法，对大家解决问题具有一定的参考价值，需要的朋友们下面随着小编来一起学习吧！ 问题描述

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

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