Do I need keys/indexes for a 3column table?

[Randell D.]

Folks,

My internet access is intermitent until I get my own connection inside
the next ten days - I say this so that I can thank in advance who ever
gives a few seconds to read/answer my query...

Basically - I have three columns in my table... product name hash,
product tax code and a unique numeric record id.

A product can contain one or more taxes hence this table is a
relationship table that will

1) allow me to search all taxes for a single product or
2) all products that are tied to a specific tax

and I will use the unique numeric id if I want to delete one or more of
the taxes tied to a single product.

My question:
Should I have keys/indexes for each of the columns? Is it one of those
questions that is best answered with six of one and half a dozen of the
other? I believe in this case because the table is so small and likely
only to contain a couple of thousand records at maximum, it would be
unhealthy to have indexes as maintaining them would create a bit of an
overhead. What though, if the table were to contain a larger amount of
records (say, in the millions) but retain the same number of columns -
what would be the best advice in this case?

I'd be grateful for a reply to the newsgroup (so all can learn) and I'll
find somewhere to read the reply over the next days.

Much appreciated,
Randell D.

[Aggro]

Randell D. wrote:

Should I have keys/indexes for each of the columns?
If you want to search data from the table and use certain column to
identify which rows should be returned, then that column should have
index, unless you think that having index causes too slow inserts or
takes too much hard drive space. In normal situations slow inserts and
hard drive space are not issues.

Also, normally searches in one table are quite fast and last under 0
seconds with less than 10 000 rows. Search in multiple tables with joins
can be hours or 0 seconds, depending on whether you have indexes or not
on the columns you are using in search.

According to your specifications: 1) allow me to search all taxes for a single product or
2) all products that are tied to a specific tax

You need to have two indexes in that table. One for product id and one
for tax id.

[Gordon Burditt]

>Basically - I have three columns in my table... product name hash,

product tax code and a unique numeric record id.
If you want the database to enforce uniqueness on the record id
(e.g. it's an auto_increment) , you need a unique index on that
column.

Is (product name hash, product tax code) unique? Although a product
can have more than one tax and a tax can have more than one product,
can the SAME product have the SAME tax twice? Do you need to enforce
this? If so, you need a unique index on either (product name hash,
product tax code) or (product tax code, product name hash).
A product can contain one or more taxes hence this table is a
relationship table that will

1) allow me to search all taxes for a single product or
2) all products that are tied to a specific tax
What is the relative frequency of these types of queries?
You will still be able to do both, but if you only need one
type a few times during annual tax preparation, it may not be
worth having an index for only that.
and I will use the unique numeric id if I want to delete one or more of
the taxes tied to a single product.

My question:
Should I have keys/indexes for each of the columns? Is it one of those
questions that is best answered with six of one and half a dozen of the
other? I believe in this case because the table is so small and likely
only to contain a couple of thousand records at maximum, it would be
unhealthy to have indexes as maintaining them would create a bit of an
overhead. What though, if the table were to contain a larger amount of
records (say, in the millions) but retain the same number of columns -
what would be the best advice in this case?

Indexes take time to maintain WHEN YOU MAKE CHANGES (or when you
add or delete them). How often will you be changing the table,
compared to how often you make queries? Is disk space a big issue?
You might want to try queries with and without the indexes (You can
add and delete indexes with ALTER TABLE) and see if it makes enough
difference to matter. Certainly for a million records it would
save a lot in disk I/O.

If you aren't going to make a lot of changes to the table, go for the
indexes. If the speed of the changes is not a big deal, but the speed
of the lookups is (someone's waiting on the phone for a quote),
go for the indexes. If changes are about 10 times as frequent as lookups,
you may want only the index on the unique record id.

Gordon L. Burditt

[Randell D.]

Gordon Burditt wrote:

Basically - I have three columns in my table... product name hash,
product tax code and a unique numeric record id.

If you want the database to enforce uniqueness on the record id
(e.g. it's an auto_increment) , you need a unique index on that
column.

Is (product name hash, product tax code) unique? Although a product
can have more than one tax and a tax can have more than one product,
can the SAME product have the SAME tax twice? Do you need to enforce
this? If so, you need a unique index on either (product name hash,
product tax code) or (product tax code, product name hash).

A product can contain one or more taxes hence this table is a
relationshi p table that will

1) allow me to search all taxes for a single product or
2) all products that are tied to a specific tax

What is the relative frequency of these types of queries?
You will still be able to do both, but if you only need one
type a few times during annual tax preparation, it may not be
worth having an index for only that.

and I will use the unique numeric id if I want to delete one or more of
the taxes tied to a single product.

My question:
Should I have keys/indexes for each of the columns? Is it one of those
questions that is best answered with six of one and half a dozen of the
other? I believe in this case because the table is so small and likely
only to contain a couple of thousand records at maximum, it would be
unhealthy to have indexes as maintaining them would create a bit of an
overhead. What though, if the table were to contain a larger amount of
records (say, in the millions) but retain the same number of columns -
what would be the best advice in this case?

Indexes take time to maintain WHEN YOU MAKE CHANGES (or when you
add or delete them). How often will you be changing the table,
compared to how often you make queries? Is disk space a big issue?
You might want to try queries with and without the indexes (You can
add and delete indexes with ALTER TABLE) and see if it makes enough
difference to matter. Certainly for a million records it would
save a lot in disk I/O.

If you aren't going to make a lot of changes to the table, go for the
indexes. If the speed of the changes is not a big deal, but the speed
of the lookups is (someone's waiting on the phone for a quote),
go for the indexes. If changes are about 10 times as frequent as lookups,
you may want only the index on the unique record id.

Gordon L. Burditt

Thanks for those comments - it gave food for thought and I think I will
use the indexes since there will be fewer changes and more reads using
one column to lookup the other.

Thanks!
Randell D.