Indexing Strategy to Increase performance

[Arnielover]

Hi guys i have a database that requires an indexing strategy to increase performance and i have to justify my reasons, what information would you need to help me with this?

[kris109]

If the database is currently running and is being accessed by applications, I would start with looking at the top 10 queries that are consuming the maximum amount of resources. Start indexing the tables.. measure the cost (explain plan) before and after indexing.. and go from there.

Also start running STATSPACK taking a database snap every fifteen minutes. Generate the report for the snapid's that begin and end during the period of PEAK USAGE. From the report you can figure out what quries you need to tune to improve performance.

[Arnielover]

thanks kris, im only familiar with the basics of indexing, its for an assignment at uni so its not an active database that people are using.

[slimdave]

Ah, in that case you can ignore all the real-world complexities, and just index the columns which appear as predicates or joins in the required reports.

Needless to say, this gives an academic solution of no practical value.

[Arnielover]

thats the sort of thing im looking for thanks slim dave, they dont teach us anything to do with the real world at uni

If i were to post the create table statements for the tables that need indexing would that be enough information for you to advise me in which tables to inex?

[slimdave]

No, not really -- you need to know what queries are going to be executed against the schema.

The only thing you can say from the schema itself is that primary key columns and foreign key columns should be indexed, but that is trivial (as the academicians say).

[Arnielover]

does that mean i have to index all the foreign keys as the promary keys have already been given a unique index. What about composite foreign keys?

[tamilselvan]

Nothing is going to help you.
Indexing strategy should be considered at the time of development, not after the system went live.
Tamil

[jmodic]

Originally posted by tamilselvan
Nothing is going to help you.
Indexing strategy should be considered at the time of development, not after the system went live.
Tamil

ROTFL!

Originally posted by Arnielover
its for an assignment at uni so its not an active database that people are using.

[slimdave]

Originally posted by Arnielover
does that mean i have to index all the foreign keys as the promary keys have already been given a unique index. What about composite foreign keys?

Realistic answer: It depends.

Academic answer: Index all foreign key columns -- index composite foreign keys with composite indexes.