Re: horrendous query challenge :-)

On Thu, 30 May 2002, Fran Fabrizio wrote:

Looking at your function, it looks like you're doing something some
databases allow you to do, mainly sending parameters to views. Knowing
this, and the fact that your function creates a self-join. Now think
about it for a second. You already know in this query the parent id
you're looking for: s.site_id, right? Drop the function, and make it
an exists query. Basically you're now asking, "as a parent, does
this site_id have a type of 's' and a child in the wm table?" The
planner gets more info, and it just might help. Try this... it's ugly,
but it's there:

SELECT wm.entity_id, e.type, e.name, w.interface_label,
wm.last_contact AS remote_ts, s.name, r.name
FROM entity_watch_map wm, entity e, site s,
region r, watch w
WHERE wm.last_contact > "timestamp"(now() - 180)
AND wm.current = false
AND wm.msg_type = w.msg_type
AND wm.entity_id = e.entity_id
AND e.active = true
AND EXISTS (
SELECT 1 FROM entity p, entity c
WHERE p.entity_id = s.site_id AND c.entity_id = wm.entity_id
AND p.type = 'S' AND c.lft BETWEEN p.lft AND p.rgt )
AND s.region_id = r.region_id
ORDER BY wm.last_contact desc, r.name, s.name;

Man, is that an ugly query. I've seen worse, though.

--
+-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-+
| Shaun M. Thomas INN Database Administrator |
| Phone: (309) 743-0812 Fax : (309) 743-0830 |
| Email: sthomas(at)townnews(dot)com AIM : trifthen |
| Web : www.townnews.com |
| |
| "Most of our lives are about proving something, either to |
| ourselves or to someone else." |
| -- Anonymous |
+-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-+