ADD INDEX ​

This command adds an index to a table.

Syntax ​

bash
ALTER TABLE <table> ADD [<Main>] INDEX [<Index_Name>] (<key>,<Agregate>)[INNER | LEFT JOIN myschema.TableY ON (Join conditions)]

<table> ​

The table you want to add an index on.

<Main> (optional) ​

The MAIN option acts as if all columns are duplicates. This means all rows and columns are stored in the Indexima K-store.

<Index_Name> (optional) ​

Users can define an index name. When a user doesn't define an index name, the default name is <table><#>  where  <#> is the next available index number (starting at 0).

<Index Definition> ​

The index can contain:

  • any column of the altered table.
  • any formula ( using build-in functions) based on one or several column names.
  • any basic aggregations: Sum, Min, Max, Avg, Count.
bash
ALTER TABLE myschema.my_table ADD INDEX ( Col1, Year(Col2), Sum(Col3));

PreJoin index: Index using an extra table (with a JOIN) ​

An index can also contain one or more joins.

The index can contain:

  • any column of the altered table.
  • any column of the joined tables.
  • any formula ( using build-in functions) based on one or more column names of the altered table or the joined table.
  • any basic aggregate: Sum, Min, Max, Avg, Count.
  • Join can only be LEFT or INNER.
bash
ALTER TABLE myschema.mytable ADD Index (mytable.col1, join_table1.colN, Sum(mytable.col2))
JOIN join_table1
     on (mytable.Col3= join_table1.Col3bis)
;

Output ​

This command does not return anything.

INFO

Adding an index exactly similar to an already existing index will trigger an error, in order to prevent duplicates index in memory.