Re: Range Access for Multiple-Part Indexes

[Derek Tsui]

There are thousands of tables in the same format. All independant.

I'm not too familiar with summery tables. How would I redesign the tables?

The subquery always contain x, y, month. Always grouped by x, y, month.

Our server has 14GB RAM. We cannot depend too heavily on key cache though. There's too many tables.

Some hardware spec. We are running 8 cores, each 2.0GHz. RAID 1+0 on 10 x 750GB 7200RPM SATA drives.

I thought about switching IN/BETWEEN with code but that's bringing the query planner to a higher level. Something I am trying to avoid as it results in countless hours of trial and error to find the 'sweet spot' of the switch. Although these tables are readonly, the amount of data varies from 1 to 50 million rows.

Yup. x, y, year are dense ranges. But month is not.