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@townnews.com    AIM  : trifthen                      |
| Web  : www.townnews.com                                             |
|                                                                     |
|     "Most of our lives are about proving something, either to       |
|      ourselves or to someone else."                                 |
|                                           -- Anonymous              |
+-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-+