Re: Highly obscure and erratic

On Wed, 19 Jun 2002, Martijn van Oosterhout wrote:

> select * from dbmedia, wdmedia a, wdmedia b
> where id = a.id and a.word='word1'
> and   id = b.id and b.word='word2'
> etc

Ewww, self join.  I'd actually suggest he keep the intersect, but use
the join format for each.  So he'd get this:

SELECT *
  FROM dbmedia d, wdmedia w
 WHERE w.word = 'word1' AND d.id=w.id
INTERSECT
SELECT *
  FROM dbmedia d, wdmedia w
 WHERE w.word = 'word2' AND d.id=w.id
INTERSECT
...

I think you get the picture from this.  If he didn't need INTERSECT to
emulate AND for his word matches, he could just use OR, and do this:

SELECT *
  FROM dbmedia d, wdmedia w
 WHERE w.word IN ('word1', 'word2', 'etc') AND d.id=w.id;

My guess is the OR version is faster, but he wants to restrict the data,
not add to it.  Unfortunate.  ^_^

> and avoid IN, INTERSECT and UNION at all costs.

Actually, if INTERSECT and UNION are done properly (which I'm guessing
they are) at most, the query has to be run once for each distinct query
between the INTERSECT/UNION clauses.  On a fast/small query, an upper
bound of doubling execution time isn't too bad.  Mathematical matrix
intersections and unions are pretty fast/easy, so that doesn't add much
overhead at all.

It's IN you have to avoid.  The way postgres does it is just insane, and
you'll suffer greatly upon using it.

--
+-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-+
| 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              |
+-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-+