Re: Slow date comparison

rudy wrote:
>
> Running Red Hat 7.0 kernel 2.2.16
> PostgreSQL 7.0.3
> Front End App is Cold Fusion
>
> We only have about 22000 rows in the article table and it will grow quite a bit
> larger.
> When I turn debugging on (cold fusion) this query takes a while. Almost 3
> sec...
>
> rstDispThread (Records=6, Time=2982ms)
> SQL =
> SELECT a.id_article,
>            a.subject,
>            getNumPostsInThread(a.id_article,0) AS numPosts,
>            isThreadNew(a.id_article,'2001-01-2617:28:37',0) AS  intThreadNew,
>            a.is_hidden,
>            a.is_locked,
>            a.reply_to
>       FROM ARTICLE a, USER_TAB ut
>      WHERE a.id_article IN (75254,76255,79262,84264,94273,94277)
>        AND a.author = ut.id_user
>   ORDER BY a.date_create ASC

Don't use IN ( ... ), use EXISTS.

See the FAQ for more details, but IN won't use an index.

Cheers,
                    Andrew.
--
_____________________________________________________________________
           Andrew McMillan, e-mail: Andrew@catalyst.net.nz
Catalyst IT Ltd, PO Box 10-225, Level 22, 105 The Terrace, Wellington
Me: +64 (21) 635 694, Fax: +64 (4) 499 5596, Office: +64 (4) 499 2267