Very Computer

I have a 4 column table with 8 million rows.  The key is 3 of the
columns(one of them is a date).  The table is very slow to query.  Will
creating an index on those three columns help?  any other thoughts.

--
==============================
Larry Schenavar
Infrastructure Specialist /DB
Electronic Data Systems, Inc

==============================

It would depend on the uniqueness of the three columns.  If they are unique
or there is very little duplication then an index would probably be a good
idea.  A good rule of thumb (but not necessarily written in stone) is if you
will have to hit 30% or more of the rows, then you should probably do a full
table scan.

You might consider just creating the index and testing to see if it improves
performance.  You could always drop the index later.

Hope this helps.

Patrick Flahan

----------------------------------------------------------------------------
------------------------

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).

--

Canberra                            02 6239 4247

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