Index question: Select * with WHERE clause. Where and how to create index

Question

Hi,

I’m working on optimizing some of my queries and I have a query that states: select * from SC where c_id ="+c_id” The schema of ** SC** looks like this:

SC (  c_id int not null,  date_start date not null, date_stop date not null, r_t_id int not null,  nt int,  t_p decimal,   PRIMARY KEY (c_id, r_t_id, date_start, date_stop));

My immediate bid on how the index should be created is a covering index in this order:

INDEX(c_id, date_start, date_stop, nt, r_t_id, t_p)

The reason for this order I base on:

The WHERE clause selects from c_id thus making it the first sorting order. Next, the date_start and date_stop to specify a sort of “range” to be defined in these parameters Next, nt because it will select the nt Next the r_t_id because it is a ID for a specific type of my r_t table And last the t_p because it is just a information.

I don’t know if it is at all necessary to order it in a specific way when it is a SELECT ALL statement. I should say, that the SC is not the biggest table. I can say how many rows it contains but a estimate could be between <10 and 1000.

The next thing to add is, that the SC, in different queries, inserts the data into the SC, and I know that indexes on tables which have insertions can be cost ineffective, but can I somehow create a golden middle way to effective this performance.

Don't know if it makes a different but I'm using IBM DB2 version 9.7 database

Sincerely

Mestika

Answer 1

Since you are talking about a maximum of 1000 lines, don't bother too much with the index, except when you have a problem in production. I have never seen a 1000 line table to be a problem at all. BTW, you configured C_ID as the primary key. Therefore you don't need an extra index on that, since the table is already sorted using the primary key.