Re: Query tuning help

Quoting Russell Smith <mr-russ(at)pws(dot)com(dot)au>:

> On Mon, 9 May 2005 11:49 am, Dan Harris wrote:
> > On May 8, 2005, at 6:51 PM, Russell Smith wrote:
> [snip]
> > select distinct em.incidentid, ea.recordtext as retdata, eg.long, eg.lat
> > FROM em
> > JOIN ea ON em.incidentid = ea.incidentid --- slight paraphrase /Mischa.
> > AND em.entrydate between '2005-1-1' and '2005-5-9'
> > AND ea.recordtext like '%RED%' AND ea.recordtext like
'%CORVETTE%'

> > Here's the situation:
> > Due to the format of the systems with which I integrate ( I have no
> > control over these formats ), we will get these 'recordtext' values one
> > line at a time, accumulating over time. The only way I can find to
> > make this work is to insert a new record for each line. The problem
> > is, that when someone wants to search multiple keywords, they expect
> > these words to be matched across multiple records with a given incident
> > number.
> >
> > For a very simple example:
> >
> > IncidentID Date Recordtext
> > -------------- -------------
> > 11111 2005-05-01 14:21 blah blah blah RED blah blah
> > 2222 2005-05-01 14:23 not what we are looking for
> > 11111 2005-05-02 02:05 blah CORVETTE blah blah
> >

select em.incidentid, ea.recordtest as retdata
from em
join ( -- equivalent to "where incidentid in (...)", sometimes faster.
select incidentid
from em join ea using (incidentid)
where em.entrydate between '2005-1-1' and '2005-5-9'
group by incidentid
having 1 = min(case when recordtest like '%RED%' then 1 end)
and 1 = min(case when recordtest like '%CORVETTE%' then 1 end)
) as X using (incidentid);