Simple Indexes

A simple index is a secondary index that has only one indexed field and zero or more included fields. Simple indexes enable you to optimize queries that filter and sort on a single field. If all fields referenced in a query are either indexed or included fields in a simple index, then you can process the query by reading only the index.

Sort Order

MapR Database sorts simple indexes on the single indexed field. MapR Database sorts the indexed field values in ascending order by default, although you can specify a descending order when you create the index. Sorting indexes benefits your ORDER BY queries because the index eliminates the need for a SORT operator in the query plan.

Simple Index Examples

The following CLI commands demonstrate how you can create various types of simple indexes. For these examples, assume that you have a MapR Database JSON table with the following sample data:

{
   "_id": "10000",
   "FullName": {
      "LastName": "Smith",
      "FirstName": "John"
   },
   "Address": {
      "Street": "123 SE 22nd St.",
      "City": "Oakland",
      "State": "CA",
      "Zipcode": "94601-1001"
   },
   "Gender": "M",
   "AccountBalance": 999.99,
   "Email": "john.smith@company.com",
   "Phones": [
       {"Type": "Home", "Number": "555-555-1234"},
       {"Type": "Mobile", "Number": "555-555-5678"},
       {"Type": "Work", "Number": "555-555-9012"}
   ],
   "Hobbies": ["Baseball", "Cooking", "Reading"],
   "DateOfBirth": "10/1/1985"
}
CLI Command Description 
maprcli table index add -path /people \
   -index emailIdx \
   -indexedfields Email
  • Creates a simple index on the Email field, with no included fields
  • Enables you to filter on the Email field
 
maprcli table index add -path /people \
   -index dobIdx \
   -indexedfields DateOfBirth \
   -includedfields FullName
  • Creates a simple index, with an included field
  • Enables you to filter on DateOfBirth and project on FullName 
maprcli table index add -path /people \
   -index LastNameIdx \
   -indexedfields FullName.LastName:-1
  • Creates a simple index on the FullName.LastName subfield, as a descending sort key
  • Allows you to filter on FullName.LastName and sort on the subfield in descending order
 
maprcli table index add -path /people \
   -index fullNameIdx \
   -indexedfields FullName
  • Creates a simple index on the nested document field FullName
  • Allows you to perform equality lookups on both the LastName and FirstName subfields of FullName
 
maprcli table index add -path /people \
   -index hobbiesIdx \
   -indexedfields Hobbies
  • Creates a simple index on the Hobbies array field
  • Allows you to filter for a specific list of hobbies
 
maprcli table index add -path /people \
   -index hobbyIdx \
   -indexedfields Hobbies[]
  • Creates a simple index using the container field path Hobbies[]
  • Allows you to filter for a specific hobby
 
maprcli table index add -path /people \
   -index phoneNumberIdx \
   -indexedfields Phones[].Number
  • Creates a simple index on the container field path Phones[].Number
  • Allows you to filter for a phone number, regardless of whether it is a home, work, or cell phone