Execution plan for MERGE tables

[Ioannis Drosidis]

Hello everyone,

I have a problem with the execution plan chosen by the query optimizer on a merge-engine table.

DESCRIPTION OF PROBLEM
----------------------
I'm working on a "live" system where data keeps coming in continuously, but only the last few months of it is required by the application.
I have created the following tables.

data_default
data_200709
data_200708
data_200707
etc ...

The default table includes data (very few) that for any reason cannot be allocated to any other table. On top of these tables I have a merge-engine table that includes the most recent tables (plus the default). All of the tables have exactly the same indexes. I have also done OPTIMIZE TABLE on all of them.

Now I'm trying to optimize a slow query. If I do EXPLAIN for this query and for each one of the tables (explain select ... from data_200709 where ...), I see that the query is using different indexes on every table (which is normal because every table has different data).

However, If I do EXPLAIN on the merge table (explain select ... from data_merge where ...) I see another set of indexes (which happen to be the same as those used at the "default" table (of my tables above), even when this default table is not included in the merge!!! BTW, this is the wrong set of indexes for the rest of the tables.

So it appears that the query optimizer optimizes correctly the query on every sub-table but wrongly on the merge table.

As a side note, the query does not use any aggregate functions, it is a simple select.

QUESTIONS
---------
1. If the optimal indexes and/or execution plan for a query is different for every table included in the merge-engine table, will the query optimizer use the best execution plan for each table, or will it use the same plan for all tables? If it uses the same plan, how does it choose it?

2. Is anyone aware of any documentation (book or online) that addresses issues related to MERGE-specific optimization?

Any help or ideas will be much appreciated.
Thanks,
Yannis