FW: Indexes efficiency
With IDS2000 all indexes are detached, so this will be for all indexes. I
did the following -
database sysmaster;
select count(*) from sysmaster:sysptprof where dbsname = "my database"
.....and this count is more or less equal to the following added together
database <my database>;
select count(*) from systables where tabtype = "T";
select count(*) from sysindexes;
I did not go into detail; excluding system catalogues, etc. .........
Dirk
-----Original Message-----
From: owner-informix-list@iiug.iiug.org
[mailto:owner-informix-list@iiug.iiug.org]On Behalf Of Parker, Jack
Sent: Friday, November 17, 2000 6:59 PM
To: Dirk Moolman; informix-list
Subject: RE: Indexes efficiency
Is that true for all indexes or just detached indexes?
cheers
j.
> -----Original Message-----
> From: Dirk Moolman [mailto:dirkm@reach.co.za]
> Sent: Friday, November 17, 2000 4:22 AM
> To: informix-list
> Subject: FW: Indexes efficiency
>
>
> You can also see your index usage by looking at
> sysmaster:sysptprof. I
> don't know about version 7, but IDS2000 gives you the table
> and the index
> hits here. I've been looking at this to see which of my
> tables / indexes
> get the highest hits (reads, writes, etc.)
>
> Dirk
> Reach Technologies
>
>
> -----Original Message-----
> From: owner-informix-list@iiug.iiug.org
> [mailto:owner-informix-list@iiug.iiug.org]On Behalf Of Rudy Fernandes
> Sent: Thursday, November 16, 2000 7:31 PM
> To: informix-list@iiug.org
> Subject: Re: Indexes efficiency
>
>
> Leonids.Voroncovs@dati.lv wrote:
>
> > Hi All. Help needed.
> >
> > Suppose we have table with heaps of documents:
> > CREATE TABLE heap (
> > ...
> > series INT NOT NULL,
> > n_from INT NOT NULL,
> > n_to INT NOT NULL,
> > ...
> > );> > I want to find a heap where document with given series and
> number lies:
> > SELECT * FROM heap WHERE series = :s AND :n BETWEEN n_from AND n_to;> > I'm thinking about two indexes:
> > CREATE INDEX idx1 ON heap( series, n_from, n_to );> > and
> > CERATE INDEX idx2 ON heap( series, n_to, n_from );
> >
> > My questions:
> > 1. Which index will be better (faster)?
> > 2. How can i see an index using frequency in production environment?
> >
> > Regards, Leonid.
>
> 1. Given idx1, I don't think idx2 adds any "query
> improvement" value. It
> will definitely slow down inserts/updates/deletes.
>
> 2. If there's either of the following possibilities, an index
> on (n_from,
> n_to, series) could help
> a. Series is not a very unique column.
> b. the WHERE clause of a query might use a wild card or a
> BETWEEN on
> series. Or even might not include it.
>
>
> This is a sort of table that you'd probably want to monitor
> in production
> and revise indexing strategies as data fills in.
>
> Index Usage. If your indexes are detached (default in
> IDS2000), you get
> retrieve read/write stats on them from sysmaster:sysptprof.
> Keep in mind,
> though, that the reads statistics are affected, a bit, by
> INSERTS/UPDATES/DELETES to the underlying table - that is,
> when an INSERT
> is done, the creation of the corresponding index row results in some
> "reads".
>
> Rudy
>