Re: large table help needed

Dave McRae wrote:
>
> I think it will, Larry. It won't hurt to try anyway, as you can always drop
> the index. The reason that I'm a little unsure is because I've not indexed
> a date field before. I have also a 3-field (all numerics) concatenated
> primary key on one large table (~300K records) and it ran like a dog without
> indexes. I quickly added 6 indexes and it goes like a dream (6 being, 1 for
> each field, 1 for the first 2 of 3 fields (a common query), another for the
> 1st and 3rd of the 3 (another very common join) and 1 for all 3. - I don't
> have 2nd and 3rd as it's not a valid (for business reasons) join. Also, I
> should have more indexes being for the 2nd and 1st, and for 3rd and 1st
> (note the order) - but I'm careful I get the joins right (I check using
> explain table).
>
> --
> David McRae dmcrae_at_dynamite.com.au
> Canberra 02 6239 4247

Dave, please take this a constructive critism. I think you have about 3 indexes that aren't helping at all.
These are the column combinations (and order) that would make the most sense of what you have installed (assuming there is no PK constraint, which would also create an index):

1,2,3
1,3
2
3

The index on just column 1 can be satisfied by 1,3 or 1,2,3 efficiently. The index on 1,2 can be satisfied efficiently by 1,2,3 index.

Any index with exact duplication of a column list, starting at the left, is redundant, almost always unnecessary, and causes needless overhead on inserts, updates, deletes, or loads.

I also wouldn't set up indexes on both 1,2 and 2,1 (as you were initially considering). 2,1 would only be of value if you had a query with a where condition for 2 (but not 1), but selected column 1 in the SELECT list. This would allow for an index-only read scenerio. If this were true, I'd replace the index on 2 with an index on 2,1

merle_martell_at_cargill.com Received on Fri May 08 1998 - 13:44:00 CDT