It seems like OPTIMIZER take into account the order of indexes in the table.

Description

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..

Environment

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

Assignee

Sergei Petrunia

Reporter

mattl

Fix versions

Affects versions

Priority

Critical
Configure