maria-discuss team mailing list archive

[Sergei Golubchik <serg@xxxxxxxxxxx>]

Hi, pslawek83!

On Feb 17, pslawek83 wrote:
> Hi Guys,
> any comments on this issue? It seems that partial unique indexes can't be used in joins.
> https://mariadb.atlassian.net/browse/MDEV-5663

Please, show EXPLAIN EXTENDED (and SHOW WARNINGS after it) for this
query of yours.

> * Why the server is treating same indexes differently depends on if
> they're UNIQUE or not

It doesn't, there're almost no differences between UNIQUE and non-unique
indexes from the optimizer point of view. Optimizer knows that for a
unique index there can be at most one matching row (if all index parts
are known and are NOT NULL), for non-unique indexes there can be more.
That's basically the only difference.

> * That's probably not optimizer issue, as we can't FORCE the index

You cannot force the index if it is not applicable at all.

> * What's internal difference between unique and non-unique index (eg.
> memory / file representation / data structure)

none.

> * What each index type is suitable for, considering query optimization
> (as there's no data i was able to find on topic)

Aria only supports BTREE indexes anyway.

Regards,
Sergei