Re: extra info - curious delay on view/where

Hi, Andre,

On Thu, 28 Oct 2004 11:53:25 -0300
"andre.toscano" <andre(dot)toscano(at)uol(dot)com(dot)br> wrote:

> Could an INDEX speed up that SELECT?

> > > CREATE VIEW "stock_available" as
> > > SELECT * FROM stock_details
> > > WHERE available = true AND visible = true AND
> > > location not in (SELECT descr FROM ignored);

Yes, I'm shure.

I would try to create (on the underlying table) a conditional index on
the column "location" with the condition "available = true AND visible =
true".

As often, the acutal effect depends on the count of rows the query
returns compared to the total rows in the table. And it would be helpful
to know the typical queries (especially the rows in the WHERE clauses)
to give additional hints on creating indices. A matching index also
potentially speeds up ORDER BY queries.

HTH,
Markus

--
markus schaber | dipl. informatiker
logi-track ag | rennweg 14-16 | ch 8001 zürich
phone +41-43-888 62 52 | fax +41-43-888 62 53
mailto:schabios(at)logi-track(dot)com | www.logi-track.com