Liquibase Change Types createIndex

The Index in the database is the same as an index in a book, these are special tables that are used to search data in the database tables. The Index table stores data and a pointer to the actual record. The INDEX has its own space in the hard disk.

Liquibase provides createIndex Change Type to create an index on an existing column or a set of columns.

Create Index

<?xml version="1.1" encoding="UTF-8" standalone="no"?>
<databaseChangeLog xmlns="http://www.liquibase.org/xml/ns/dbchangelog" xmlns:ext="http://www.liquibase.org/xml/ns/dbchangelog-ext" xmlns:pro="http://www.liquibase.org/xml/ns/pro" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xsi:schemaLocation="http://www.liquibase.org/xml/ns/dbchangelog-ext http://www.liquibase.org/xml/ns/dbchangelog/dbchangelog-ext.xsd http://www.liquibase.org/xml/ns/pro http://www.liquibase.org/xml/ns/pro/liquibase-pro-latest.xsd http://www.liquibase.org/xml/ns/dbchangelog http://www.liquibase.org/xml/ns/dbchangelog/dbchangelog-latest.xsd">
	<changeSet author="wesome" id="1692599548266-1">
		<createTable catalogName="appledb" tableName="apple">
			<column autoIncrement="true" name="apple_id" type="BIGINT">
				<constraints nullable="false" primaryKey="true" />
			</column>
			<column name="apple_name" type="VARCHAR(255)" />
			<column defaultValueComputed="CURRENT_TIMESTAMP" name="current_date_time" type="datetime" />
		</createTable>
	</changeSet>
	<changeSet author="wesome" id="1692599548266-2">
		<createIndex catalogName="appleDb" tablespace="appleDb" schemaName="appleDb" unique="true" indexName="apple_name_indx" tableName="apple">
			<column name="apple_name" />
		</createIndex>
	</changeSet>
</databaseChangeLog>

liquibase.properties (update changeLog File file format with SQL, XML, YAML, JSON as per changelog.mysql.)

changeLogFile=changelog.mysql.xml
liquibase.command.url:jdbc:mysql://localhost:3306/appleDb?createDatabaseIfNotExist=true&autoReconnect=true&useSSL=false&allowPublicKeyRetrieval=true
liquibase.command.username : root
liquibase.command.password : rootroot

Run command liquibase update

index will be created in the table on the apple_name column and can be verified by running the query

SHOW INDEXES FROM appleDb.apple;

Create Index with Column Order

Liquibase uses the order of columns listed in createIndex Change Type while creating Index.

<?xml version="1.1" encoding="UTF-8" standalone="no"?>
<databaseChangeLog xmlns="http://www.liquibase.org/xml/ns/dbchangelog" xmlns:ext="http://www.liquibase.org/xml/ns/dbchangelog-ext" xmlns:pro="http://www.liquibase.org/xml/ns/pro" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xsi:schemaLocation="http://www.liquibase.org/xml/ns/dbchangelog-ext http://www.liquibase.org/xml/ns/dbchangelog/dbchangelog-ext.xsd http://www.liquibase.org/xml/ns/pro http://www.liquibase.org/xml/ns/pro/liquibase-pro-latest.xsd http://www.liquibase.org/xml/ns/dbchangelog http://www.liquibase.org/xml/ns/dbchangelog/dbchangelog-latest.xsd">
	<changeSet author="wesome" id="1692599548266-1">
		<dropColumn catalogName="appledb" columnName="current_date_time" schemaName="appledb" tableName="apple">
		</dropColumn>
	</changeSet>
</databaseChangeLog>

liquibase.properties (update changeLog File file format with SQL, XML, YAML, JSON as per changelog.mysql.)

changeLogFile=changelog.mysql.xml
liquibase.command.url:jdbc:mysql://localhost:3306/appleDb?createDatabaseIfNotExist=true&autoReconnect=true&useSSL=false&allowPublicKeyRetrieval=true
liquibase.command.username : root
liquibase.command.password : rootroot

Run command liquibase update

Liquibase will generate sql

--  Changeset changelog.mysql.xml::1692599548266-1::wesome
CREATE TABLE appledb.apple (apple_id BIGINT AUTO_INCREMENT NOT NULL, apple_name VARCHAR(255) NULL, current_date_time datetime DEFAULT NOW() NULL, CONSTRAINT PK_APPLE PRIMARY KEY (apple_id));

--  Changeset changelog.mysql.xml::1692599548266-2::wesome
CREATE UNIQUE INDEX apple_name_date_indx ON appleDb.apple(apple_name, current_date_time);

--  Changeset changelog.mysql.xml::1692599548266-3::wesome
CREATE UNIQUE INDEX date_apple_name_indx ON appleDb.apple(current_date_time, apple_name);

Create Clustred Index

a Clustered Index is a specific table where the key and pointer to the actual record in the database are stored. The Cluster Index makes sure the record in the actual database stores as per the order of the cluster table. In a relational database, each table can have only one clustered index which is mostly the primary key of the table, which dictates the order of records in the table.

If the clustered attribute is set to true, then Liquibase will create a clustered index, it will dictate the order in which records will be stored in a table.
If clustered attribute is set to false, then Liquibase will create a non-clustered index, where the order of the rows will not match the order of the actual records.

<?xml version="1.1" encoding="UTF-8" standalone="no"?>
<databaseChangeLog xmlns="http://www.liquibase.org/xml/ns/dbchangelog" xmlns:ext="http://www.liquibase.org/xml/ns/dbchangelog-ext" xmlns:pro="http://www.liquibase.org/xml/ns/pro" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xsi:schemaLocation="http://www.liquibase.org/xml/ns/dbchangelog-ext http://www.liquibase.org/xml/ns/dbchangelog/dbchangelog-ext.xsd http://www.liquibase.org/xml/ns/pro http://www.liquibase.org/xml/ns/pro/liquibase-pro-latest.xsd http://www.liquibase.org/xml/ns/dbchangelog http://www.liquibase.org/xml/ns/dbchangelog/dbchangelog-latest.xsd">
	<changeSet author="wesome" id="1692599548266-1">
		<createTable catalogName="appledb" tableName="apple">
			<column autoIncrement="true" name="apple_id" type="BIGINT">
				<constraints nullable="false" primaryKey="true" />
			</column>
			<column name="apple_name" type="VARCHAR(255)" />
			<column defaultValueComputed="CURRENT_TIMESTAMP" name="current_date_time" type="datetime" />
		</createTable>
	</changeSet>
	<changeSet author="wesome" id="1692599548266-2">
		<createIndex catalogName="appleDb" clustered="true" tablespace="appleDb" schemaName="appleDb" unique="true" indexName="apple_name_indx" tableName="apple">
			<column name="apple_name" />
		</createIndex>
	</changeSet>
</databaseChangeLog>

liquibase.properties (update changeLog File file format with SQL, XML, YAML, JSON as per changelog.mysql.)

changeLogFile=changelog.mysql.xml
liquibase.command.url:jdbc:mysql://localhost:3306/appleDb?createDatabaseIfNotExist=true&autoReconnect=true&useSSL=false&allowPublicKeyRetrieval=true
liquibase.command.username : root
liquibase.command.password : rootroot

Run command liquibase update

Liquibase Change Types createIndex

Create Index

Create Index with Column Order

Create Clustred Index

follow us on