I created two test table. The only difference between them is the order of secondary index.
And I explained same query on two table after insert same test data in two tables.
But query execution plan is different.
<< First see the below test case >>
Here's the problem.
If "ix_fd1_fd2" index is UNIQUE, optimizer never use "ix_fd_fdpk" index in this case. Becuase UNIQUE index will be positioned before NORMAL secondary index like below table, even though it is added later.
How can I make optimizer use ix_fd1_fdpk to avoid filesort operation (Without index hint ^^) ?
And is this expected ?
==> The last query doesn't need filesort operation when they use ix_fd1_fdpk index. But not..
Linux matt001 2.6.18-308.el5 #1 SMP Tue Feb 21 20:06:06 EST 2012 x86_64 x86_64 x86_64 GNU/Linux