Index Creation on Table with 30 Million Rows
Posted on 2011-02-11
Have a table with approx. 30 million rows in it. It has 4 columns (3 int's, one date) plus a PK. I need to be able to join on the 4 columns when doing an insert to make sure the row isn't already there. I also need to be able to group on any combination of the 4, sort on any one and possible group by 2 1 or more.
I originally had an index on each column but the index's alone were eating up 32GB of space. I switched to an additional index on the PK with the 4 columns included and the index size dropped 12GB but I've noticed a significant decrease in select performance. Now I'm thinking of creating an index using all 4 columns (as opposed the index of the PK with the 4 columns included, I hope that makes sense). But I've always thought that the only way that will give me good performance is if I'm using all 4 columns to join, sort, group, etc.
Am I basically stuck with a choice between performance and index size? that if I want good performance I need to create a sep. index on each and just eat the disk space?