Re: Range Access for Multiple-Part Indexes
Posted by:
Rick James
Date: January 01, 2010 04:37PM
Keep the PRIMARY KEY. It serves as a UNIQUEness constraint.
Separate single-column indexes would not help; possibly would hurt.
OK, AVG(AVG) makes sense in your case.
I guess there is some optimization I am not familiar with that makes the IN work better than BETWEEN. In both cases, it cannot do much more than look at a range of x values. (x is the first field in the index it picked, and x is not constrained to '='.)
You say that the tables are readonly, once created. That should make them excellent for summarizing. Hmmm... you say tableS. Are there other tables like this?
SELECT x, y, month, AVG(value) AS value FROM tbl
Do the subquery always look like this? Or might it be something other than x,y,month?
How much RAM do you have? You have a 4GB key_buffer, but this table needs no more than 0.33GB.
If you are building the SELECT with code, you could switch between IN and BETWEEN depending on how many items are asked for.
Are x, y, year each always dense ranges? That is, can it always be done with BETWEEN?