Rewrite MySQL query without using UNION

Question

I have the following query which returns the correct results but I'm sure it's not the best way to get these results...

select * from (
    select * from features where feature_area = 0 
    order by updateStamp desc limit 1
) as feature_1

union all

select * from (
    select * from features where feature_area = 1 
    order by updateStamp desc limit 1
) as feature_2

union all

select * from (
    select * from features where feature_area = 2 
    order by updateStamp desc limit 1
) as feature_3

This returns results which look something like...

id    feature_area        title                  updateStamp
--------------------------------------------------------------------
103   0                   This is a title        2014-04-15 09:26:14
102   1                   Another title          2014-03-27 14:09:49
98    2                   More title             2014-01-21 16:00:55

Could this be improved using joins rather than unions and if so could you point me in the right direction please.

EDIT:

Having looked at the other options pointed out by @Ben it would seem I've already got the quickest query (albeit not that attractive) for my particular purpose. Feel free to correct me if you think I'm wrong though. I'm no expert, hence I'm asking for advice.

@Ben That answer would suggest I've got the best approach already?? — Tom, Apr 22 '14 at 12:43
It might @Tom, yes. Only you can decide whether you have the best approach. If what you have is quick enough then you don't need to change anything. If it's not quick enough then try the other options. If you still can't get it quick enough come back here with this question but including your indexes, table DDL, and the explain plan for each method you've tried and someone _might_ be able to help. — Ben, Apr 22 '14 at 12:45
@Tom the answers below use two select statements with a group by. This may save database time rather than performing 3 individual select statements and the combining the result sets. If you are worried about performance you can always look at the stats and see which one performs better. If you go with an approach below, Indexes can further speed things up. — sarin, Apr 22 '14 at 13:04
@Tom will updatestamp be unique per group, i.e. to the millisecond? — Anthony Horne, Apr 22 '14 at 13:25

score 0 · Answer 1 · answered Apr 22 '14 at 12:57

0

select f.* from features f
inner join (
    select
    feature_area
    max(updateStamp) as updateStamp
    from
    features 
    where feature_are IN (0,1,2)
    group by feature_area
) sq  on sq.feature_area = f.feature_area 
and sq.updateStamp = f.updateStamp

answered Apr 22 '14 at 12:57

fancyPants

50,732
33
89
96

Thanks for this... working with my data which is currently only 100ish rows, using the UNION ALL method is marginally quicker. I like this approach though and I've learnt stuff from it so thanks. – Tom Apr 22 '14 at 13:03
1

These solutions will be index dependent, so it might be worth optimising the indexes for both queries before drawing conclusions based on which is marginally quicker. – Kickstart Apr 22 '14 at 14:50

score 0 · Answer 2 · answered Apr 22 '14 at 12:58

Hope I read your question correctly.

select *
From features f
inner join ( select feature_area, max(updateStamp) as maxUpdateStamp
             from features
             Group by feature_area
            ) as minfeatures
           ON minfeatures.feature_area = f.feature_area
           AND minfeatures.maxUpdateStamp = f.updateStamp

Anthony Horne · Answer 3 · 2014-04-22T13:28:29.267

0

with MaxFeature
AS
(
select 
    feature_area        AS  feature_area        
    ,max(updateStamp)   AS  MaxUpdateStamp
from 
    features
group by 
    feature_area
)
select
    Features.*
from
    Features
inner join 
    Maxfeature
on
    Features.feature_area = MaxFeature.feature_area
and
    Features.updateStamp = MaxFeature.MaxUpdateStamp
order by    
    Features.feature_area asc

edited Apr 22 '14 at 13:28

answered Apr 22 '14 at 13:06

Anthony Horne

2,522
2
29
51

1

Max updateStamp makes sense, but which `id` and `title` will you return? – Marcus Adams Apr 22 '14 at 13:09
Good Point @MarcusAdams. I forgot. But. the above update will work if there updateStamp is usually at least unique per feature (the closest millisecond) – Anthony Horne Apr 22 '14 at 13:29

score 0 · Answer 4 · edited May 23 '17 at 12:28

Assuming proper indexes, it's often most performant to solve this with the anti-join:

SELECT f1.*
FROM features f1
LEFT JOIN features f2
  ON f2.feature_area = f1.feature_area
  AND f2.updateStamp < f1.updateStamp
WHERE f1.feature_area < 3
  AND f2.id IS NULL
ORDER BY f1.feature_area

In cases where there are duplicate rows with same feature_area and highest updateStamp, it will return duplicate rows.

For more explanation of this technique: Get records with highest/smallest <whatever> per group

Rewrite MySQL query without using UNION

4 Answers4