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