[MDEV-25848] Support for Multi-Valued Indexes Created: 2021-06-03 Updated: 2023-11-01 |
|
| Status: | Open |
| Project: | MariaDB Server |
| Component/s: | Data types, JSON |
| Fix Version/s: | None |
| Type: | Task | Priority: | Major |
| Reporter: | Eldad Fux | Assignee: | Unassigned |
| Resolution: | Unresolved | Votes: | 5 |
| Labels: | Compatibility, compat80 | ||
| Description |
|
Hey, our team has the desire to start storing JSON arrays of strings inside our MariaDB instance and we would love to take advantages of them with queries using the JSON_CONTAINS() function. This design can help us avoid multiple tables for managing many-to-many relations and making slow & complex joins over huge datasets. The problem is that without proper indexing of this new JSON fields we can't really take advantage of them properly. While checking MySQL feature set, we have seen that version > 8 has support for multi-valued Indexes (https://dev.mysql.com/doc/refman/8.0/en/create-index.html#create-index-multi-valued), is there anything similar in MariaDB for arrays? is this feature expected any time in the near future? Appreciate the help, this can be a game changer for our use-case, and our team will be more then happy to help with feedback, testing or other possible ways. �� |
| Comments |
| Comment by Marko Mäkelä [ 2021-06-03 ] |
|
Multi-valued index is also called inverted index. Some storage engines already implement a special case of that, for FULLTEXT INDEX. The idea is that multiple index records can point into a single record. Because there are a number of design problems with the InnoDB implementation of FULLTEXT INDEX, I think that it might be useful to implement more generic inverted indexes, to not only replace the FULLTEXT INDEX implementation but also to cover indexes built on functions that can return multiple values. It would be a huge effort. |