About Composite Index

Question

When defining a composite index, e.g.

create table temptable (id integer, id2 integer, name string, INDEX ci using plain(id2, id));

The id and id2 are indexed in elasticsearch using integer, but the I see from ES's _mapping is like:

      "ci" : {
        "type" : "string",
        "analyzer" : "standard"
      },

Where both id and id2 are copied to ci with type "string".

Can you explain more about this (like the order preserved) and probably a bit more the whole composite index thing in crate data?

score 2 · Accepted Answer · answered Jul 29 '14 at 13:14

You found 2 bugs by doing this which we'll try to fix asap. ;)

First, using a plain index type should result in the 'keyword' analyzer not the 'standard' one.

Second, a composite over 2 non-string columns shouldn't result in a string typed column, but if supported, in the same type of the origin columns. I've wrote "if supported" because for now, we'd forbid defining a composite index over non-string columns because we don't know what this would be for.
Our current match function implementation only supports string literals, so this function couldn't be used for querying the composite index.
Can you explain your use-case a bit?
Maybe creating an issue at github would make sense for this possible enhancement.

The order of the columns used for defining the composite index doesn't matter at all, in case of a string, values of both are analyzed and resulting terms will be inserted/merged at the target field.

Thanks for reporting!

In my cases, the data set is a combination of numeric fields and text fields. And the query we want to support may have filters across multiple numeric fields and text fields. e.g., in the table created in the question, we would like to have some query like: select * from temptable where id>* and id2=* and match(name,'*'); Certainly, without composite index, the backend can still do separate filter and join the result together like what the ES does. — Newair, Jul 29 '14 at 15:27
But in my mind, a composite filter can always make things faster, similar to a skip-scan vs. index (if you consider the first index as a "table" and second index as a "index") (Some different opinion: http://stackoverflow.com/questions/22617674/does-elasticsearch-have-compound-indexes) But I am not sure how this can be done with ES as the only backend (for non-string cases). So not sure if it's reasonable to create an issue. — Newair, Jul 29 '14 at 15:27

About Composite Index

1 Answers1