Re: Range Access for Multiple-Part Indexes

[Rick James]

What indexes do you have?

To assist in analyzing slow SELECTs, please provide
* SHOW CREATE TABLE tbl\G -- engine, indexes
* SHOW TABLE STATUS LIKE 'tbl'\G -- sizes
* EXPLAIN SELECT ...\G -- clues of inefficiencies
* SHOW VARIABLES LIKE '%buffer%'; -- cache size
and surround them with [ code ] and [ / code ]

BETWEEN may have tricked it into using a different index. Or maybe there should be an index beginning with year.

Are you taking an average of an average? That is usually mathematically wrong! Please justify it or switch to SUM and COUNT, then taking SUM(SUM)/SUM(COUNT).

How fast does it run as a single query instead of using a subquery?

Suggest you build and maintain summary tables, probably indexed by (year, month, x, y) and having sum(value) and count(*).

The PRIMARY KEY is useful only for fetching by x (the first field), since it is multivalued (IN).

There is no index on the subquery, so there is no optimization there.

It is usually inefficient to split dates into separate fields. Recommend you switch from the two columns year and month to a single field computed as, for example, 12*year+month.