mysql more efficient way to query

Question

below gets the job done but I feel like I am violating programming rule.

select *
from serverTable
where server like ‘proc1@server1’
order by reg_date desc
limit 1
union
select *
from serverTable
where server like ‘proc2@server1’
order by reg_date desc
limit 1
union
select *
from serverTable
where server like ‘proc3@server1’
order by reg_date desc
limit 1
union
select *
from serverTable
where server like ‘pro4@server1’
order by reg_date desc
limit 1
union
select *
from serverTable
where server like ‘proc5@server1’
order by reg_date desc
limit 1

Is there better way or correct way to get this done?

Tim Biegeleisen · Accepted Answer · 2016-08-17 16:15:32Z

1

One option would be to find the maximum reg_date for each server group, where server can take one of the 5 values in your UNION query, and then select the full records from your table for each server group.

SELECT t1.*
FROM serverTable t1
INNER JOIN
(
    SELECT server,
           MAX(reg_date) AS reg_date
    FROM serverTable
    WHERE server in ('proc1@server1', 'proc2@server1', 'proc3@server1', 'proc4@server1', 'proc5@server1')
    GROUP BY server
) t2
    ON t1.server = t2.server AND
       t1.reg_date = t2.reg_date

edited Aug 17, 2016 at 16:15

answered Aug 17, 2016 at 16:13

Tim Biegeleisen

526k32 gold badges324 silver badges399 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Robert Columbia · Accepted Answer · 2016-08-17 16:08:12Z

0

USE UNION ALL rather than UNION. By using UNION, the database is unnecessarily looking for duplicates that don't exist. All of your UNIONed queries include a WHERE clause like "server like (some unique value)". server can't be equal to more than one of these, so you won't ever have any duplicates to filter out.

Also, use = instead of like. LIKE is used when you want to do a wildcard search, which you are not doing.

answered Aug 17, 2016 at 16:08

Robert Columbia

6,45115 gold badges34 silver badges42 bronze badges

Comments

John · Accepted Answer · 2016-08-17 17:30:03Z

0

Something like this should do it:

select
  serverTable.col1,
  serverTable.col2,
  serverTable.col3,
  max(reg_date)
from
  serverTable
where
  server in ('proc1@server1','proc2@server1','proc3@server1','proc4@server1','proc5@server1')
group by
  serverTable.col1,
  serverTable.col2,
  serverTable.col3
order by
  server, reg_date;

answered Aug 17, 2016 at 17:30

John

4,0545 gold badges41 silver badges74 bronze badges

1 Comment

user3502374 Over a year ago

sorry, I haven't had a chance to look at this. but will tonight and come back to this

Collectives™ on Stack Overflow

mysql more efficient way to query

3 Answers 3

Comments

Comments

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related