Unique indexes not used in JOINs / lacking DOCs

Description

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)

Query 2:

  • We change FORCE INDEX to FORCE INDEX(raw_stats_lookup_idxx)
    Or

  • remove force index.

=> The non-unique index is used.

Query 3:
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.

Questions:

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

Environment

Debian-70-wheezy, 2xHDD in RAID1, 16GB Memory

Assignee

Unassigned

Reporter

Slawomir Pryczek

Labels

None

Affects versions

Priority

Minor
Configure