Complex Indexes
The following example demonstrates indexing of multiple table fields, indexing of nested fields, and the use of "filtering" predicates during index scans.
sql-> create index idx_state_city_income on
Persons (address.state, address.city, income);
Statement completed successfully
sql-> SELECT * from Persons p WHERE p.address.state = "MA"
and income > 79000;
> Row 0
+-------------+------------------------------------------------+
| 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 |
+-------------+------------------------------------------------+
1 row returned Index idx_state_city_income is applicable to the above query. Specifically, the state = "MA" condition can be used to establish the boundaries of the index scan (only index entries whose first field is "MA" will be scanned). Further, during the index scan, the income condition can be used as a "filtering" condition, to skip index entries whose third field is less or equal to 79000. As a result, only rows that satisfy both conditions are retrieved from the table.