Queries that Benefit from Secondary Indexes

Secondary indexes benefit queries with filter conditions, ORDER BY clause, and projections.

They benefit these query elements in the following ways:

Filter Conditions
Eliminates full table scans, reducing the number of documents that MapR Database reads, if the filtering fields are keys in an index.
ORDER BY Clause
Eliminates the need to sort the data after scanning the index, if the index's sort order matches the query's sort order.
Projections
Eliminates the need to read the MapR Database JSON table, if all fields referenced in the query are fields in an index.
NOTE The projections optimization is not supported for OJAI queries that execute through the OJAI Distributed Query Service, and SQL queries issued to MapR Drill. See OJAI Distributed Query Service for details about the types of OJAI queries that use the service.

The following topics describe the specific query types and provide examples.