UNION and subselect both performed better than EXISTS for this particular case.
On Thu, Nov 21, 2013 at 12:31 PM, desmodemone <desmodem...@gmail.com> wrote: > Hi Robert, could you try with "exists" ? > > SELECT c.* > FROM contacts c > WHERE exists ( SELECT 1 FROM phone p WHERE p.addr =? and p.contact_id= > c.id ) > OR exists (SELECT 1 FROM email e WHERE e.addr = ? and e.contact_id=c.id); > > > > > > 2013/11/21 Robert DiFalco <robert.difa...@gmail.com> > >> I have found this: >> >> SELECT c.* >> FROM contacts c >> WHERE c.id IN ( SELECT p.contact_id FROM phone p WHERE p.addr = ? ) >> OR c.id IN (SELECT e.contact_id FROM email e WHERE e.addr = ? ); >> >> To have a worse plan than: >> >> SELECT * FROM contacts where id IN ( >> ( SELECT c.id FROM contacts c >> JOIN phone p ON c.id = p.contact_id AND p.addr = ? >> UNION >> SELECT c.id FROM contacts c >> JOIN email e ON c.id = e.contact_id AND e.addr = ? ); >> >> Maybe this is no surprise. But after discovering this my question is >> this, is there another option I dont' know about that is logically the same >> that can perform even better than the UNION? >> > >
