How and when to create Indexes?

general discussion
1

Hi all,

I’m workin’ on a DWH and it has never been indexed…
the big problem is that reporter, when analysing the query, is never ending… or it’s ending but it takes very very very long time…
I’ve noticed that indexation could resolve that problem… but I don’t excel in indexation and I don’t known how to do it well.

Because it s a DWH, I think I can create a lot of indexes. But how?
Do I have to create an index with all the column of a primary keys? (some of the primary keys are made of 7 fields…)
And another on the forein keys?
And another on the usual search fields (used in the WHERE clause.)?

Or just one with all that field? Or only one?
It’s a DB2 database but I think indexation rules are quite universal…

Could you help me on that?

Toby :fr: (BOB member since 2005-10-28)

2

Simply put… all primary keys should have a primary key (unique) index on them. All foreign keys (primary key values in a different table) should have an index on them. All columns used in joins should participate in an index. Any column likely to be used in a where clause is an excellent candidate for an index. If you’re running on a database with no indexes you may not only have performance issues but data integrity issues as well. :shock:

Dave Rathbun :us: (BOB member since 2002-06-06)

3

You’ve got it! This DWH is a disaster… no constraints, no indexes, relationnal rules are not respected (this is a not a “star” database)… Beside my BO works I’m working on indexing these tables to increase the performance and I’ve already had good results :slight_smile: But sometimes I’m lost on what to do… :?

Thank you very much for your anwser, I’ll follow your advices :slight_smile:

Toby :fr: (BOB member since 2005-10-28)

4

Just one more question about the indexes

Figure out I’ve got a table like that

Table 1
Field 1
Field 2
Field 3
Field 4
Field 5

Field 1,2,3 are the primary key and used in joins, I’ll create one unique index on the 3 fields.

Field 1,2 and 5 are used in a join. Shoud I create an index on that 3 fields too? even if 1 and 2 are already in the first index?

Still considering the first index:
Field 2 is used alone in a join, should i create an index on that field?

Same question for field 4…

Should i really create an index for each field participating in joins or where? I fear i could decrease the performance…

Toby :fr: (BOB member since 2005-10-28)

5

If this is a data warehouse, then your performance considerations are for retrieval, not for inserts / updates / deletes. :slight_smile: Indexes help in that area. If you have a query with F1+F2+F3 and a query uses F1+F2+F5 then it may use the first index, it may not. It really depends on the query parsing process for your database. Some parsing engines are not very sophisticated… they will only evaluate a certain number of index combinations and then give up, ignoring what might be a better index choice.

What you’re looking for is a good DBA. :slight_smile:

Dave Rathbun :us: (BOB member since 2002-06-06)

6

Once again, you’re right … I’ve to find a good DBA, but where is he??? :wink:
Yes I’m alone to do all that work and if I 'm trying to index the database (that has never been indexed…) myself that’s because I’ve got Reporter queries that never ends… and I’m sure that a good indexation could resolve that.
It’s a real big problem!

Toby :fr: (BOB member since 2005-10-28)

7

What database are you using? Most databases will allow you to “explain” queries and see what the query plan is. By examining specific queries you can see what indexes are (or are not) being used. If you are returning a subset (less than 5%) of the data then it’s fairly easy to tune… you want to avoid full table scans at all cost. :slight_smile: If you’re returning a medium amount (5-25% of the data) then sometimes it’s a bit trickier. If you’re returning over 25% of the data then it can actually be more efficient to not have indexes on criteria, but you will want to have your join columns indexed.

Dave Rathbun :us: (BOB member since 2002-06-06)