Re: Index Or-ing & And-ing------WebKitFormBoundary

> High cardinality column first is better for the optimizer, however, if you > are running IDS v11.50 or later (see I told you version info is helpful) the > optimizer can take advantage of the new Index Self Join feature to use such > an index even for queries that only specify the second and following > columns, but not the lead column. Art, Actually, this feature is present in IDS 11.10. FC3, as you can see from this sqexplain (table and column names changed to protect the innocent... ^_^) QUERY: (OPTIMIZATION TIMESTAMP: 11-16-2009 17:27:40) ------ select col1, col2, col3, col4 from table1 where col4 > 5000 and col5 < 6500 Estimated Cost: 614017 Estimated # of Rows Returned: 1525155 1) informix.table1: INDEX PATH (1) Index Keys: col5 col4 (Serial, fragments: ALL) Index Self Join Keys (col5 ) Lower Index Filter: informix.table1.col5 = informix.table1.col5 AND informix.table1.col4 > 5000 Upper Index Filter: informix.table1.col4 < 6500 This table has 13 million records, of which 11 million had a null col4. created an index on col4 without knowing column cardinality, and it took about an hour to return results. created an index on col5 (which has a better balanced cardinality - there are 1325 possible values with an average of 10000 records per value) and the results came in 1 minute and change. Big difference! My 0.02... Ramon ------WebKitFormBoundaryNnreJpc+oxePAGbj