MySQL Prefix index

Prefix index

This index query creates an index column in the string or character column. MySQL system can create multiple indexes in the table. It will create in a table as the primary and secondary keys. MySQL secondary index stores value in a different data structure. It takes more memory and space on the disk. As a result, the system slows down the data operation.

Syntax

The length type decides the data type for an index or column, and the number of characters decides the non-binary data type. The CHAR, VARCHAR, and TEXT data type column decides length based on the characters. The number of bytes decides the binary data type. The BINARY, VARBINARY, and BLOB data type column decides length based on the bytes. 

The basic syntax of the prefix index shows below.

MySQL EXPRESSION column name (length)

The syntax of the prefix index with the CREATE TABLE command is shown below.

 CREATE TABLE tbl_name (
 Column1 INT NOT NULL PRIMARY KEY,
 Column2 INT NOT NULL,
 Column3 INT NOT NULL,
 Column4 varchar(45),
 Index (column name (length))
 ); 

The syntax of the prefix index into the existing table is shown below.

 CREATE INDEX name
 ON table name (column name (length)); 

The prefix index must use the column prefix key parts for BLOB and TEXT columns. If you use CHAR, VARCHAR, BINARY, and VARBINARY index, then it is optional to create column prefix key parts.

The prefix length depends on the MySQL storage engine. For example, the InnoDB storage engine supports a length of up to 767 bytes. The MyISAM storage engine supports a length of up to 1,000 bytes.

Examples of the prefix index

1) Example: The basic prefix index example shows below.

Execute the below query to create a new table and indexes. Here, the name column createsan index with length. The primary key must be assigned as an index. You create either a single or multiple prefix index in a single table.

 mysql> create table index_table(
 roll_number INT NOT NULL PRIMARY KEY,
 name VARCHAR(10) NOT NULL,
 mark text NOT NULL,
 img blob NOT NULL,
 INDEX (roll_number, name(10))); 

OUTPUT

Execute the following query to get the index of the table.

mysql> show index from index_table;

The above output image shows indexes from the table. It contains the key name, column name, index type, and visibility of the table.

2) Example: The primary prefix index on the existing table example shows below.

Execute the below query to create an index with length. The column assigns TEXT data type on a table.

mysql> create indexmark ON index_table(mark (100));

OUTPUT

Execute the following query to get the index of the table.

mysql> show index from index_table;

The above output image shows indexes from the table. It contains the key name, column name, index type, and visibility of the table.

3) Example: The prefix index on the existing table shows below.

Execute the below query to create an index with length. The column assigns BLOB data type on a table.

mysql> create indeximg ON index_table(img (400));

OUTPUT

Execute the following query to get the index of the table.

mysql> show index from index_table;

The above output image shows indexes from the table. It contains the key name, column name, index type, and visibility of the table.

4) Example: The basic prefix index with database example shows below.

Execute the below query to create an index with length. The column assigns TEXT data type on a table.

mysql> create indexmark ON tutorial.index_table(mark (200));

OUTPUT

Execute the following query to get the index of the table.

mysql> show index from index_table;

The above output image shows indexes from the table. It contains the key name, column name, index type, and visibility of the table.