Re. Indexes

} If I have following table: } table A customer char(10), } inv_num int, } bill_data date, } ship_date date, } bill_amt real, } print_flag char(1) } } cust_index on (customer) } cust_index2 on (customer, inv_num) } cust_index3 on (bill_date) } } Question 1: } select count(*) from A where customer = "1111" and print_flag <> "A" } } How does on-line search table A? Will it get all the records from } table A where customer = "1111" using index, than search records base on } print_flag within these records? For example: We have 200,000 records in } table A and 50 records with customer = "1111". Will the above select } statement will get 50 records from table than search base on print_flag on } these 50 records only?. It all depends... as usual ... on whether you've run UPDATE STATISTICS on table A. YOU know you've only got 50 records for customer '1111', but unless you give it some clues (ie. distribution data from update statistics), the optimiser won't. If you set sqexplain on before you run the query, you'll be able to tell what the optimiser thinks is going on. } } Question 2: } select count(*) from A where customer = "1111" and bill_date = "10/1/96" } } Will about sql will use cust_index and cust_index3 to get records? } Or do I need to create new index on (customer, bill_date)? Ditto as above. fyi if you do decide a new index is the way to go, the best index (so I'm told by various Informix tech people) is to put the column with the lowest number of different values at the head of the index. For example, if you have loads of rows with bill_date = '10/1/96', your new index should be (bill_date, customer) not (customer, bill_date). } } Thanks } } ----- } Susik Lee } Skechers USA } email: susikl@skechers.com } phone: (310) 318-3100x1285 } Hope this helps. Peter Whittleton (whittle@ssax.com)