Description:
If I perform an Analize table on a sub table of a Merge Table then the Optomizer fials to use the correct index in a query. If I then perform an Analize Table on the Master table then the Analize function reports it doesn't support Merge tables BUT DOES ACTUALLY fix the problem.
How to repeat:
+----+-------------+--------------+------+-------------------+-----------+---------+-------------------------+----------+----------------------------------------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+--------------+------+-------------------+-----------+---------+-------------------------+----------+----------------------------------------------+
| 1 | SIMPLE | Members | ref | PRIMARY,AccountNo | AccountNo | 9 | const | 7 | Using where; Using temporary; Using filesort |
| 1 | SIMPLE | Cards | ref | PRIMARY,MemberNo | MemberNo | 9 | texaco.Members.MemberNo | 2 | Using where |
| 1 | SIMPLE | Transactions | ALL | CardNo | NULL | NULL | NULL | 12854337 | Using where |
+----+-------------+--------------+------+-------------------+-----------+---------+-------------------------+----------+----------------------
+---------------------+---------+----------+----------------------------------------------------------+
| Table | Op | Msg_type | Msg_text |
+---------------------+---------+----------+----------------------------------------------------------+
| texaco.Transactions | analyze | note | The storage engine for the table doesn't support analyze |
+---------------------+---------+----------+----------------------------------------------------------+
+----+-------------+--------------+------+-------------------+-----------+---------+-------------------------+------+----------------------------------------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+--------------+------+-------------------+-----------+---------+-------------------------+------+----------------------------------------------+
| 1 | SIMPLE | Members | ref | PRIMARY,AccountNo | AccountNo | 9 | const | 7 | Using where; Using temporary; Using filesort |
| 1 | SIMPLE | Cards | ref | PRIMARY,MemberNo | MemberNo | 9 | texaco.Members.MemberNo | 2 | Using where |
| 1 | SIMPLE | Transactions | ref | CardNo | CardNo | 20 | texaco.Cards.CardNo | 344 | |
+----+-------------+--------------+------+-------------------+-----------+---------+-------------------------+------+----------------------------------------------+
