Index creation

Informix® supports the creation of indexes on collections and relational tables by using the MongoDB API and the wire listener.

Index creation by using the MongoDB syntax

For JSON collections and relational tables, you can use the MongoDB createIndex and ensureIndex syntax to create an index that works for all data types. For example: 
db.collection.createIndex( { zipcode: 1 } )
db.collection.createIndex( { state: 1, zipcode: -1} )
Tip: If you are creating an index for a JSON collection on a field that has a fixed data type, you can get the best query performance by using the Informix® extended syntax.
The following options are supported:
  • name
  • unique
The following options are not supported:
  • background
  • default_language
  • dropDups
  • expireAfterSeconds
  • language_override
  • sparse
  • v
  • weights

Index creation for a specific data type by using the Informix® extended syntax

You can use the Informix® createIndex or ensureIndex syntax on collections to create an index for a specific data type. For example: 
db.collection.createIndex( { zipcode : [1, $int] } )
db.collection.createIndex( { state: [1, $string],  zipcode: [-1, $int] } )
This syntax is supported for collections only. It not supported for relational tables.
Tip: If you are creating an index on a field that has a fixed data type, you can get better query performance by using the Informix® createIndex or ensureIndex syntax.
The following data types are supported:
  • $bigint
  • $binary
  • $boolean
  • $date
  • $double2
  • $int3
  • $integer3
  • $lvarchar1
  • $number2
  • $string1
  • $timestamp
  • $varchar
Notes:
  1. $string and $lvarchar are aliases and create lvarchar indexes.
  2. $number and $double are aliases and create double indexes.
  3. $int and $integer are aliases.

Index creation for arrays using the Informix extended syntax

You can use the Informix® createIndex or ensureIndex syntax on collections to create an index for arrays. For example: 
db.collection.createIndex( { "my_array" : [ 1, "$array", "$int" ] } )
which creates an integer array index on the field named “my_array?.

This syntax is similar to the Informix extended typed syntax. Specify the type of the index as “$array? and then provide a third argument specifying the data type stored in the array.

Note: This syntax is supported for collections only. It is not supported for relational tables.
The following data types are supported with array indexes:
  • $bigint
  • $date
  • $double1
  • $int2
  • $integer2
  • $number1
  • $varchar
Notes:
  1. $number and $double are aliases and create double indexes.
  2. $int and $integer are aliases.

Index creation for text, geospatial, and hashed

Text indexes
Text indexes are supported. You can search string content by using text search in documents of a collection.
You can create text indexes by using the MongoDB or Informix® syntax. For example, here is the MongoDB syntax: 
db.articles.ensureIndex( { abstract: "text" } )
The Informix® syntax provides additional support for the Informix® basic text search functionality. For more information, see createTextIndex.
Geospatial indexes
2dsphere indexes are supported by using the GeoJSON objects, but not the MongoDB legacy coordinate pairs.
2d indexes are not supported.
Hashed indexes
Hashed indexes are not supported. If a hashed index is specified, a regular untyped index is created.

For more information about the MongoDB features, see http://docs.mongodb.org/manual/reference/.