Re: Indices

Robert Sparks writes:- > I have a table (540,000 records) with 4 columns : timestamp,site,channel,value > I used SQL to add an index on (site,channel) > > Here is the output from SET EXPLAIN ON for three queries: > ************************************************************** > QUERY: > ------ > select site,channel from data order by site,channel;> > 1) rjsparks.data: INDEX PATH > (1) Index Keys: site channel > QUERY: > ------ > select distinct site,channel from data order by site,channel;> Temporary Files Required For: Order By > > 1) rjsparks.data: SEQUENTIAL SCAN > > the first query returned almost instantaniously. > I interrupted the second two after letting them run for .5 hour or so. > > Why is informix not using the indexes. Do i need to do something to tell it about > the created index? No you don't need to tell it about the index. The Informix optimiser makes an attempt to find the best way of retreiving the information requested. In the first query you asked for all rows in index order so it used the index to provide the result, reading the index, in index order, to retreive the rows so producing a quick response. In the second query you requested a subset of the table (all unique values of site channel). B-tree duplicate indexes are not set up to provide links to the next higher value index item only to the next item in the linked list. Because of this the optimiser has to make a choice whether to use the index to read all rows and retrieve in order or to scan the data file quickly picking out the unique values and then sorting them. As scanning the index is slower than scanning the table optimisers will normally chose the latter unless the number of duplicate values is known and is low enough to make using the index more effecient. (In fact the Informix optimiser may always choose this method it depends how intelligent it is). > Second question : > After playing around, I dropped the created index. The idx file was touched, but > did not shrink. (It has gotten quite large.) Is there a way to tell the engine > to relinquish some disk space? > You are using Standard engine which uses C-ISAM as the underlying file handler. C-ISAM does not recover space from its files normally it only marks the delete rows as available for re-use. In fact some implementations do recover space if the deletes occur at the physical end of file but this would not normally occur on the .idx file only on the .dat. I think you will find that the only way to recover the space is to drop all the indexes first and then recreate them. If that doesn't work you have to drop the table and then recreate it. > Robert Sparks > rjsparks@loanstar.tamu.edu > Hope this helps. Cheers, Jim -------------------------------------------------------------------- Name: Jim Gordon Internet: jgordon@ssf-sys.DHL.COM Company: DHL Systems Inc Phone: (415) 358-5911 Address: 1700 S. Amphlett Blvd. Fax: (415) 571-6429 San Mateo, CA 94402 --------------------------------------------------------------------