Re: Adress table needed? Please give me some ideas! Thanks.

[Dave Java]

Seller
(id, name, addrId, email, phone)

Merchandise
(id, name, maker, model, addrId, price)

Address
(id, street, city, state, zip, country)

The addrId is foreign key ---> Address(id)

I am using Hibernate OR mapping.

Indexes:
Seller(id)
Merchandise(id)
Address(id)

Do I need index on Merchandise(addrId)? It seems to me that it is not useful.

For example,
For query: find all merchandise that are located in CA, US and their prices are between $70 and $120.

select m.id, m.name, m.price, a.country, a.state
from Merchandise m, Address a
where m.addrId=a.id and a.country='US' and a.state='CA'
and (m.price between 70 and 120)

Will Index on Merchandise(price) helpful?
How about index on Address(country), Address(state)?

Is the overall performance for the query above for a big Merchandise table acceptable?

Thanks for help !