Re: 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?. I don't think UPDATE STATISTICS is a requirement here. INFORMIX will either use cust_index or cust_index2 as the WHERE clause of you SELECT stmt has a column for which there is an index. >} >} 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)? > No you don't. If you know that the number of records per customer is a small % of the database (50 out of 200,000 is miniscule), then you do not need another index besides cust_index. In fact, if 50 out of 200,000 is a typical distribution of a customer, I would experiment with doing away with cust_index2 as well. >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). In fact, the reverse is true. Your most unique column should be at the head of the index. Rudy GIC, Kuwait