[Maria-discuss] Index bug in aria engine?
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 Regards,
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
Hello! Only btree, no hash indexes?
2014-03-06 13:46 GMT-03:00 Sergei Golubchik
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
_______________________________________________ Mailing list: https://launchpad.net/~maria-discuss Post to : maria-discuss@lists.launchpad.net Unsubscribe : https://launchpad.net/~maria-discuss More help : https://help.launchpad.net/ListHelp
-- Roberto Spadim SPAEmpresarial Eng. Automação e Controle
ok sergei! thanks :) maybe gsoc idea/jira task?
2014-03-06 14:33 GMT-03:00 Sergei Golubchik
Hi, Roberto!
On Mar 06, Roberto Spadim wrote:
Hello! Only btree, no hash indexes?
Aria? Yes. b-tree and r-tree, no hash indexes.
Aria only supports BTREE indexes anyway.
Regards, Sergei
-- Roberto Spadim SPAEmpresarial Eng. Automação e Controle
IMO, MariaDB (like MySQL) would better issue a warning when trying to create a hash index and the engine does not support it. I'm not surprised if someone thinks that all engines support hash :)
Regards
Federico
--------------------------------------------
Gio 6/3/14, Sergei Golubchik
Hello! Only btree, no hash indexes?
Aria? Yes. b-tree and r-tree, no hash indexes.
Aria only supports BTREE indexes anyway.
Regards, Sergei _______________________________________________ Mailing list: https://launchpad.net/~maria-discuss Post to : maria-discuss@lists.launchpad.net Unsubscribe : https://launchpad.net/~maria-discuss More help : https://help.launchpad.net/ListHelp
participants (4)
-
Federico Razzoli -
pslawek83 -
Roberto Spadim -
Sergei Golubchik