How does Index work exactly in DB2?

Since the Index work different on every of the database software. So, I'm posting a simple question here hoping for an answer.

Let's look at the WHERE clause in the SQL Query.



As you can see, if I create an index for #3 to both the CUSTOMER_ID and USER_ID for quicker response.

The question is do I have to make a sperate index for #1 and #2. (For #1 --> create index for CUSTOMER_ID and a seperate index for USER_ID while the #3 use a seperate index). Or would the #1 and/or #2 still work quickly by taking advantage of the index that was made for #3?? (Yea, may be a bit slower but faster than the one without an index).

Thanks...

P.S. Is there a better documentation somewhere on this one??

To index the table, create two indexes:

Index1: CUSTOMER_ID, USER_ID
Index2: USER_ID

Selects #1 and #3 would use the first index.

Select #2 would use the second index.

Here's some basic tips (Click Here)

The one you need to look at is:

If you create a composite (multi-column) index, try to order the columns in the key so that the WHERE clauses of the frequently used queries match the column(s) that are leftmost in the index. In other words, since the CUSTOMER_ID was used in two queries, make it the leftmost field in the index. The leftmost fields are the most important in determining the efficiency and selectivity of an index.

the first column in the index is important ..
say you have an index on (customer_id , user_id )

then #1 and #3 will work better ..

if the column order in that index is (user_id , customer_id) then #2 and #3 will work better ..

this uses the concept of B - tree ..

generally multi-column index is better than having multiple indexes , one on each column ..

did i answer your question ?

Thanks for the clarification... Will get started on it later on t his week....

I have one more question about the "CUSTOMER_ID". That would work for some tables. But suppose if one table have CUSTOMER_ID as primary key then the question is. Should I not mess with it (primary key) but create a seperate index instead? (Say for "USER_ID")...

If I'm reading your question correctly, then the answer would be "it depends". The correct answer sometimes lies in performance tuning and the acceptable responsiveness of your application.

For example. If you have a table that has a CUSTOMER_ID as a primary key then that field has a unique index on it. If you were going to search by (CUSTOMER_ID, USER_ID) or just (USER_ID) then you would definitely want an index on USER_ID for the sake of the second search.

Now do you need to create another index for (CUSTOMER_ID, USER_ID)? If on average a particular CUSTOMER_ID has only a few USER_ID associated with it, then the CUSTOMER_ID primary key index would get you very close to the records you needed. It would still have to scan through those index pages to find the USER_ID you were looking for but you would still get to the index page you needed to be on quickly.

Now if each CUSTOMER_ID had several million USER_ID associated with it, then a (CUSTOMER_ID, USER_ID) index would probably make sense because you wouldn't want to scan the entire index looking for the un-indexed USER_ID value.

Adding indexes to a table slows down Insert and Updates to that table since each of these changes have to be applied not only to the actual data but the index pages as well. It also eats up more disk space. If your database is small and your tables have few indexes on them, then adding a new index (even one that might be a bit redundant) will probably not be noticed on modern fast machines. If your data is huge, your table heavily indexed or your hardware slow, then sometimes you have to make intelligent choices about which indexes are the most important to the performance of your program. The indexes for seldom-used queries sometimes have to be removed for the sake of more important indexes and the overall performance of updates to the table.

Because action queries on a table are affected by the number of indexes that table has, a table that is updated constantly by multiple users may not be able to have as many indexes as one that stays very static.

The bottom line is sometimes you have to let the little hour glass on your screen tell you whether or not an additional index is necessary. Every database and situation is different. Performance tuning and monitoring is the best answer.

That make sense now... Thanks...