I have a very big table of customers, with the primary key being the customer id.
One of the columns is zip, i.e. zip code of the customer location. How do I declare EJB 3.0 persistence annotations that there must be an index on that column?
A very frequent query is get customers by zip. That usually returns less than 10 customers (even no customers at all), sometimes up to 70 and very seldom more than that. I don't think the query should scan the whole table to find the customers in one zip code.
A possible solution would be to declare a zip entity and declare a one to many relationship between zips and customers. But this is probably overkill as I have no satellite data associated with the zip, for our app it is only a meaningless number we group customers by. Also this solution would imply yet another table (zip_customer).
Is indeed declaring an index on the zip column the best for me? How do I do it?
