createIndex

Uses

You can typically use the createIndex Change Type when you want to add an index on one of the columns or a set of columns to increase the searchability of the records in your database.

If you set the clustered attribute to true, Liquibase will create a clustered index, which defines the order in which records are stored in a table. If you set the clustered attribute to false, Liquibase will create a non-clustered index, where the order of the rows will not match the order of the actual records.

When creating indexes, Liquibase uses the order of columns that you list in the createIndex Change Type. For example:

<changeSet author="bob" id="1_createTable_department"> <createTable tableName="department"> <column name="col_1" type="integer"/> <column name="col_2" type="integer"/> <column name="col_3" type="integer"/> </createTable> </changeSet> <changeSet author="bob" id="1_createIndex_index_one"> <createIndex indexName="index_one" tableName="department"> <column name="col_1"/> <column name="col_2"/> <column name="col_3"/> </createIndex> </changeSet> <changeSet author="bob" id="2_createIndex_index_two"> <createIndex indexName="index_two" tableName="department"> <column name="col_3"/> <column name="col_2"/> <column name="col_1"/> </createIndex> </changeSet> <changeSet author="bob" id="3_createIndex_index_three"> <createIndex indexName="index_three" tableName="department"> <column name="col_2"/> <column name="col_3"/> <column name="col_1"/> </createIndex> </changeSet>

These changesets will produce the following (the update-sql command example):

CREATE TABLE public.department (col_1 INT, col_2 INT, col_3 INT); CREATE INDEX index_one ON public.department(col_1, col_2, col_3); CREATE INDEX index_two ON public.department(col_3, col_2, col_1); CREATE INDEX index_three ON public.department(col_2, col_3, col_1);

Running the createIndex Change Type

To run this Change Type, follow these steps:

1. Add the Change Type to your changeset, as shown in the examples on this page.

2. Specify any required attributes. Use the table on this page to see which ones your database requires.

3. Deploy your changeset by running the update command: liquibase update

Available attributes

Nested tags

Nested property attributes

Database support

Changing the index type for PostgreSQL

PostgreSQL provides several index types. Each index type uses a different algorithm. You can choose the one that best fits your project’s performance needs. The PostgreSQL documentation provides all available index types and their use cases.

You can use the following example code to set the index type for your project. You'll need to set the using attribute to the index type you want to use. These examples use gin.

Setting index prefix lengths for MySQL and MariaDB

MySQL and MariaDB require a prefix length when you index a TEXT or BLOB column, and accept one on other string columns when you want to index only the first characters. Add a columnPrefix tag for each column that needs one, giving the column name and the length.

The columnPrefix tag is in the pro namespace, so an XML changelog must declare that namespace and its schema location, as the examples below do. In YAML and JSON changelogs, nest the tags within a columnPrefixes tag.

The generate-changelog command writes these tags for you when it reads an existing MySQL or MariaDB schema, so a generated changelog reproduces the source index instead of dropping the prefix length.

Note: A unique prefix index is not covered. generate-changelog writes a unique index as a constraint on the column inside createTable rather than as a createIndex change, so no columnPrefix tag is produced and the prefix length is lost.