does elasticsearch have compound indexes?

Question

i'm wondering if elasticsearch needs to have compound indexes defined a priori. by a compound index, i mean something like what mongodb has.

db.collection.ensureIndex( { field1: 1, field2: 1, field3: 1 } )

or something like what mysql db has.

create index adhoc_index on mytable(field1, field2, field3);

so the data i am dealing with is very flat (most of it is just csv format). it looks like the following (for completeness).

field1, field2, ..., fieldN

the number of fields is arbitrary. one dataset may have 10 fields, another 20, another 1000. i basically convert each row into a JSON document that looks like the following.

{
 "field1" : "value1",
 "field2" : "value2",
 ...
 "fieldN" : "valueN"
}

denote A, B, and C as three mutually exclusive subsets of the fields: {field1, field2, ..., fieldN}. at any given time, i have to build a dynamic query that filters the records for A=a, B=b, and C=c.

for example,

• A = {field1}, B = {field2, field3}, C = {field6}
• A = {field2}, B = {field1}, C = {field1000, field50}

so my elasticsearch DSL query may look something like the following (not sure if this is correct myself, but just to illustrate).

"bool" : {
 "must" : [
  {"term" : { "field1" : "val1" },
  {"term" : { "field2" : "val2" },
  {"term" : { "field3" : "val3" },
  {"term" : { "field4" : "val4" }
 ]
}

basically, this query says, "give me all the documents with field1=val1, field2=val2, field3=val3, field4=val4".

the reason why i ask this about elasticsearch is because i could not find a clear answer searching on the internet about compound indexes. are they even needed?

i'm evaluating mongodb and mysql as well, and i don't think they will work well with my situation simply because these compound/composite indexes have to be defined a priori, and i won't have that information until runtime which group of fields need to be indexed together to optimize the query speed. of course, with mysql, once i find out which group of fields need to be indexed together (and in which order), i can go back an create the index, but that may take a long time if the dataset is large (number of rows > 1 million).

do i simply get this compound index feature out of the box with elastic search? meaning, i won't even have to touch the index mapping file/definition?

Answer 1

ElasticSearch doesn't have composite indexes, but it's very efficient at querying multiple indexes and intersecting them (intersecting bit-vectors FTW).

Most of the time, composite indexes are not needed, even for cases like you mentioned where you query for 4 different fields. ElasticSearch will happily query 4 different indexes and then intersect the results in an efficient manner. In my experience its performance matches and surpasses that of MongoDB in similar situations.

If you absolutely must have a composite index, you might consider indexing an auxiliary field whose value is a composite of the values you want to index.