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?

Answer 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

Answer 2

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.

Answer 3

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;

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