which would be faster?

[sfullman]

I'm working on a database with 1,000,000+ records. It's a list of auto parts.

Currently the part appears for every year it services. There are about 200,000 actual parts, and on average each part covers a vehicle make or makes over 5 years.

If I changed the database so that there was a STartYear and EndYear field, I could compact the records down to about 1/5 the size. One record could handle multiple parts which are IDENTICAL IN EVERY OTHER WAY.

However, the query would be more complex, instead of

... and Year = 2001

you'd have

... and ( StartYear >= 2001 or EndYear <=2001)

Of course I'd index the new fields, as I would with Year if we didn't compact the records. Which would you suggest I do for speed of access?

Thanks,
Sam

[eoghain]

Actually I'd recommend that you create a new table called year, and link that to the product with another table. This would reduce the amount of data that you are storing by giving you only 1 record for multiple years, and give you the ability to have parts that skip a year. I don't know if it happens that a part would work for years 90,91,92,95,96 but with a start/end year like you are suggesting you can't account for this.

But besides that adding a little extra definition to your where clause isn't going to slow down the query like adding 5x the records to search through will.