Considering following table that isn't changing:
And another table we're trying to optimize:
We have 2 same indexes, one is unique.
Query 1, this one won't be using index AT ALL (look at force index)
We change FORCE INDEX to FORCE INDEX(raw_stats_lookup_idxx)
remove force index.
=> The non-unique index is used.
With FORCE INDEX removed, we drop the non-unique index from raw_stats_other_copy
The server won't use the unique index, even if it's EXACTLY the same as non-unique index.
Why the server is treating same indexes differently depends on if they're UNIQUE or not
That's probably not optimizer issue, as we can't FORCE the index
What's internal difference between unique and non-unique index (eg. memory / file representation / data structure)
What each index type is suitable for, considering query optimization (as there's no data i was able to find on topic)
Problems like that are actually hard to spot as there's no DOC in mariadb / mysql considering the topic.
Needs some documentation if unique indexes are treated differently in JOINS, so the users will know where it'll be beneficial to add duplicated index like that.
Debian-70-wheezy, 2xHDD in RAID1, 16GB Memory