List:General Discussion« Previous MessageNext Message »
From:Bryan Cantwell Date:September 25 2007 9:04pm
Subject:Ugly sql optimization help?
View as plain text  
I have the following horrible sql. I need one result that has all the
data in one row. I am currently using 3 sub queries and figure it must
be a better way...

							SELECT 'FS_DEV',

	
ifnull(a.severity, 0) AS aseverity, 
	
ifnull(a.eventid, 0) AS aeventid,  
	
ifnull(a.description, 'None') AS adescription,  
							ifnull(a.hostid,
0) AS ahostid,  
	
ifnull(a.color,'#E5E0DC') as acolor,  
	
ifnull(a.fontcolor,0) as afontcolor,  
	
ifnull(a.severitydesc,'None') as aseveritydesc,  
	
ifnull(p.severity, 0) AS pseverity,  
	
ifnull(p.eventid, 0) AS peventid,  
	
ifnull(p.description, 'None') AS pdescription,  
							ifnull(p.hostid,
0) AS phostid,  
	
ifnull(p.color,'#E5E0DC') as pcolor,  
	
ifnull(p.fontcolor,0) as pfontcolor,  
	
ifnull(p.severitydesc,'None') as pseveritydesc,  
	
ifnull(s.severity, 0) AS sseverity,  
	
ifnull(s.eventid, 0) AS seventid,  
	
ifnull(s.description, 'None') AS sdescription,  
							ifnull(s.hostid,
0) AS shostid,  
	
ifnull(s.color,'#E5E0DC') as scolor,  
	
ifnull(s.fontcolor,0) as sfontcolor,  
	
ifnull(s.severitydesc,'None') as sseveritydesc  
							FROM  
							(SELECT
e.severity, e.eventid, e.description,  
							e.hostid,
fs.color, fs.fontcolor, fs.severitydesc  
							FROM fs_events
e, fs_severity fs  
							WHERE hostid in
(21,22,23,24,15,16)
							and e.severity =
fs.severityid 
							AND e.event_perf
= 1 
							AND e.time_stamp
>= date_sub(now(), INTERVAL 30 DAY)  
							AND
e.acknowledged in ( 0,1)  
							ORDER BY
e.severity DESC, e.time_stamp DESC LIMIT 0, 1) p,
							(SELECT
e.severity, e.eventid, e.description,  
							e.hostid,
fs.color, fs.fontcolor, fs.severitydesc  
							FROM fs_events
e, fs_severity fs  
							WHERE hostid in
(21,22,23,24,15,16)
							and e.severity =
fs.severityid 
							AND
e.event_avail = 1 
							AND e.time_stamp
>= date_sub(now(), INTERVAL 30 DAY)  
							AND
e.acknowledged in ( 0,1)  
							ORDER BY
e.severity DESC, e.time_stamp DESC LIMIT 0, 1) a,
							(SELECT
e.severity, e.eventid, e.description,  
							e.hostid,
fs.color, fs.fontcolor, fs.severitydesc  
							FROM fs_events
e, fs_severity fs  
							WHERE hostid in
(21,22,23,24,15,16)
							and e.severity =
fs.severityid 
							AND e.event_sec
= 1 
							AND e.time_stamp
>= date_sub(now(), INTERVAL 30 DAY)  
							AND
e.acknowledged in ( 0,1)  
							ORDER BY
e.severity DESC, e.time_stamp DESC LIMIT 0, 1) s
Thread
Ordering by unrelated column in a GROUP BYRob Wultsch25 Sep
  • Re: Ordering by unrelated column in a GROUP BYPeter Brawley25 Sep
    • Ugly sql optimization help?Bryan Cantwell25 Sep
RE: Ordering by unrelated column in a GROUP BYRob Wultsch25 Sep