Skip to main content
Skip table of contents

ADD INDEX

This command adds an index to a table.

Syntax

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.

example

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.


example

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.

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



JavaScript errors detected

Please note, these errors can depend on your browser setup.

If this problem persists, please contact our support.