Search by attribute

[acrobate67]

We have a site that sells car parts. The data is already there and we used attributes to identify Year, Make and Model on which each part is available. We need to be able to query a search based on those 3 attributes. It may seem easy but we have like 60 million products if we count combinations. We tried a few add-ons but none is efficient so we are considering programming something fully custom.

A custom SQL query that returns products for one attribute is pretty easy to do and it responds very quickly (under one second). But when we put 2 attributes it gets to 15 seconds and with 3 attributes it gets to close to a minute. Before anyone says we need stronger servers, I'd like to add that we are using 2 bare metal servers (one for SQL and one for Web).

One more thing to mention: the search cannot return products cause there would be too many. We want to return all categories in which there are products and show the product count next to each category. Then when the user will click on a category, he will be sent to the product list.

Any ideas are welcome.

Thanks.