Multi-Key Indexes
A multi-key index indexes all the elements of an array, or all the elements and/or all the keys of a map. For such indexes, for each table row, the index contains as many entries as the number of elements/entries in the array/map that is being indexed. Only one array/map may be indexed.
sql-> create index idx_areacode on
Persons (address.phones[].areacode);
Statement completed successfully
sql-> SELECT * FROM Persons p WHERE
p.address.phones.areacode =any 339;
> Row 0
+-------------+------------------------------------------------+
| id | 2 |
+-------------+------------------------------------------------+
| firstname | John |
+-------------+------------------------------------------------+
| lastname | Anderson |
+-------------+------------------------------------------------+
| age | 35 |
+-------------+------------------------------------------------+
| income | 100000 |
+-------------+------------------------------------------------+
| lastLogin | 2016-11-28T13:01:11.2088 |
+-------------+------------------------------------------------+
| address | street | 187 Hill Street |
| | city | Beloit |
| | state | WI |
| | zipcode | 53511 |
| | phones |
| | type | home |
| | areacode | 339 |
| | number | 1684972 |
+-------------+------------------------------------------------+
| connections | 1 |
| | 3 |
+-------------+------------------------------------------------+
| expenses | books | 100 |
| | food | 1700 |
| | travel | 2100 |
+-------------+------------------------------------------------+
> Row 1
+-------------+------------------------------------------------+
| id | 4 |
+-------------+------------------------------------------------+
| firstname | Peter |
+-------------+------------------------------------------------+
| lastname | Smith |
+-------------+------------------------------------------------+
| age | 38 |
+-------------+------------------------------------------------+
| income | 80000 |
+-------------+------------------------------------------------+
| lastLogin | 2016-10-19T09:18:05.5555 |
+-------------+------------------------------------------------+
| address | street | 364 Mulberry Street |
| | city | Leominster |
| | state | MA |
| | zipcode | NULL |
| | phones |
| | type | work |
| | areacode | 339 |
| | number | 4120211 |
| | |
| | type | work |
| | areacode | 339 |
| | number | 8694021 |
| | |
| | type | home |
| | areacode | 339 |
| | number | 1205678 |
| | |
| | type | home |
| | areacode | 305 |
| | number | 8064321 |
+-------------+------------------------------------------------+
| connections | 3 |
| | 5 |
| | 1 |
| | 2 |
+-------------+------------------------------------------------+
| expenses | books | 240 |
| | clothes | 2000 |
| | food | 6000 |
| | shoes | 1200 |
+-------------+------------------------------------------------+
> Row 2
+-------------+----------------------------------------------+
| id | 5 |
+-------------+----------------------------------------------+
| firstname | Dana |
+-------------+----------------------------------------------+
| lastname | Scully |
+-------------+----------------------------------------------+
| age | 47 |
+-------------+----------------------------------------------+
| income | 400000 |
+-------------+----------------------------------------------+
| lastLogin | 2016-11-08T09:16:46.3929 |
+-------------+----------------------------------------------+
| address | street | 427 Linden Avenue |
| | city | Monroe Township |
| | state | NJ |
| | zipcode | NULL |
| | phones |
| | type | work |
| | areacode | 201 |
| | number | 3213267 |
| | |
| | type | work |
| | areacode | 201 |
| | number | 8765421 |
| | |
| | type | home |
| | areacode | 339 |
| | number | 3414578 |
+-------------+----------------------------------------------+
| connections | 2 |
| | 4 |
| | 1 |
| | 3 |
+-------------+----------------------------------------------+
| expenses | clothes | 1500 |
| | food | 900 |
| | shoes | 1000 |
+-------------+----------------------------------------------+
3 rows returned In the above example, a multi-key index is created on all the area codes in the Persons table, mapping each area code to the persons that have a phone number with that area code. The query is looking for persons who have a phone number with area code 339. The index is applicable to the query and so the key 339 will be searched for in the index and all the associated table rows will be retrieved.
sql-> create index idx_expenses on
Persons (expenses.keys(), expenses.values());
Statement completed successfully
sql-> SELECT * FROM Persons p WHERE p.expenses.food > 1000;
> Row 0
+-------------+--------------------------------------------+
| id | 2 |
+-------------+--------------------------------------------+
| firstname | John |
+-------------+--------------------------------------------+
| lastname | Anderson |
+-------------+--------------------------------------------+
| age | 35 |
+-------------+--------------------------------------------+
| income | 100000 |
+-------------+--------------------------------------------+
| lastLogin | 2016-11-28T13:01:11.2088 |
+-------------+--------------------------------------------+
| address | street | 187 Hill Street |
| | city | Beloit |
| | state | WI |
| | zipcode | 53511 |
| | phones |
| | type | home |
| | areacode | 339 |
| | number | 1684972 |
+-------------+--------------------------------------------+
| connections | 1 |
| | 3 |
+-------------+--------------------------------------------+
| expenses | books | 100 |
| | food | 1700 |
| | travel | 2100 |
+-------------+--------------------------------------------+
> Row 1
+-------------+--------------------------------------------+
| id | 3 |
+-------------+--------------------------------------------+
| firstname | John |
+-------------+--------------------------------------------+
| lastname | Morgan |
+-------------+--------------------------------------------+
| age | 38 |
+-------------+--------------------------------------------+
| income | 100000000 |
+-------------+--------------------------------------------+
| lastLogin | 2016-11-29T08:21:35.4971 |
+-------------+--------------------------------------------+
| address | street | 187 Aspen Drive |
| | city | Middleburg |
| | state | FL |
| | zipcode | NULL |
| | phones |
| | type | work |
| | areacode | 305 |
| | number | 1234079 |
| | |
| | type | home |
| | areacode | 305 |
| | number | 2066401 |
+-------------+--------------------------------------------+
| connections | 1 |
| | 4 |
| | 2 |
+-------------+--------------------------------------------+
| expenses | food | 2000 |
| | gas | 10 |
| | travel | 700 |
+-------------+--------------------------------------------+
> Row 2
+-------------+------------------------------------------------+
| id | 4 |
+-------------+------------------------------------------------+
| firstname | Peter |
+-------------+------------------------------------------------+
| lastname | Smith |
+-------------+------------------------------------------------+
| age | 38 |
+-------------+------------------------------------------------+
| income | 80000 |
+-------------+------------------------------------------------+
| lastLogin | 2016-10-19T09:18:05.5555 |
+-------------+------------------------------------------------+
| address | street | 364 Mulberry Street |
| | city | Leominster |
| | state | MA |
| | zipcode | NULL |
| | phones |
| | type | work |
| | areacode | 339 |
| | number | 4120211 |
| | |
| | type | work |
| | areacode | 339 |
| | number | 8694021 |
| | |
| | type | home |
| | areacode | 339 |
| | number | 1205678 |
| | |
| | type | home |
| | areacode | 305 |
| | number | 8064321 |
+-------------+------------------------------------------------+
| connections | 3 |
| | 5 |
| | 1 |
| | 2 |
+-------------+------------------------------------------------+
| expenses | books | 240 |
| | clothes | 2000 |
| | food | 6000 |
| | shoes | 1200 |
+-------------+------------------------------------------------+
3 rows returned In the above example, a multi-key index is created on all the expenses entries in the Persons table, mapping each category C and each amount A associated with that category to the persons that have an entry (C, A) in their expenses map. The query is looking for persons who spent more than 1000 on food. The index is applicable to the query and so only the index entries whose first field (the map key) is equal to "food" and second key (the amount) is greater than 1000 will be scanned and the associated rows retrieved.