Hi!

Fred> Selecting from a MERGE table may use a sub-optimal index.

<cut>

Fred> And do the SELECT again, now on the MERGE table:

mysql> explain select hex(nas_ip), session_id from test where nas_ip =
0x3e642f0d and session_id >= '0000AAD8' and session_id <= '0000AAD9'; Fred>
+-------+------+----------------------+--------+---------+-------+------+------------+ Fred> | table | type | possible_keys | key | key_len | ref | rows |
Extra Fred> | Fred>
+-------+------+----------------------+--------+---------+-------+------+------------+ Fred> | test | ref | nas_sessionid,nas_ip | nas_ip | 4 | const | 10 |
where Fred> used | Fred>
+-------+------+----------------------+--------+---------+-------+------+------------+ Fred> 1 row in set (0.00 sec)

Fred> Now MySQL decides to use the other index! This slows the query down Fred> considerably, especially on larger tables than this one. (It happens on
other Fred> queries as well. This is just a simplified table.)

Fred> The number of rows is incorrect as well. I've never seen any other number
than Fred> 10 on MERGE tables. If MySQL bases the index selection on this number,
then it Fred> seems a logical choice. Both indexes would have 10 rows and 'range' is a
more Fred> expensive search than 'ref'.

The reason for this is that we haven't had time to add range optimization for merge tables; We added keys to MERGE tables quite recently and we forgot to look at this case.

I have now added this to our TODO for 4.0

Regards, Monty