MySQL Prefix Index with Examples

In this tutorial, we will learn about the prefix index in MySQL and how to use it to maximize the performance of the queries. So, let’s get started!!

Also read: MySQL Composite Index- Multiple Column Indexes

Introduction to MySQL Prefix Index

When you create a secondary index for a column, MySQL stores the values of the columns in a different data structure, such as a B-Tree or hash.

The index will take up a lot of disc space if the columns are string columns, which could slow down INSERT operations.

MySQL enables you to make an index for the leading part of the column values of the string columns.

To create the prefix index, we simply have to examine the maximum number of unique values in the column and accordingly specify the prefix length.

Also read: MySQL Invisible Index

Syntax of MySQL Prefix Index

We can create the prefix index while creating the table using the following syntax-

CREATE TABLE table_name(
    column_name1 datatype,
    column_name2 datatype,
    column_namen datatype,
    INDEX(column_name(length))
);Code language: SQL (Structured Query Language) (sql)

If the table is already created, then you can use the following syntax to add an index to that table-

CREATE INDEX index_name
ON table_name(column_name(length));Code language: SQL (Structured Query Language) (sql)

For non-binary string types like CHAR, VARCHAR, and TEXT, the length in this syntax is the number of characters, whereas, for binary string types like BINARY, VARBINARY, and BLOB, the length is the number of bytes.

If you are creating the index for the columns having datatype BLOB or TEXT, you must specify the length of the prefix. Whereas, it is not necessary to mention the length of the prefix for the columns of type VARCHAR, CHAR, BINARY and VARBINARY.

Also, the prefix and the length of the prefix depend on the storage engines.

The maximum prefix length for InnoDB tables with REDUNDANT or COMPACT row format is 767 bytes. Whereas the prefix length is 3,072 bytes for InnoDB databases with DYNAMIC or COMPRESSED row formats. The prefix length for MyISAM tables is up to 1,000 bytes.

MySQL Prefix Index Examples

Now we will take an example to understand how to create a prefix index and how to determine the length of the prefix.

For this purpose, we will use the CLASSICMODELS database which comes by default with MySQL when you install it.

The CLASSICMODELS database consists of many tables, out of which we will use the table PRODUCTS to demonstrate the example.

Here is the table description of the PRODUCTS table.

Now, let’s write a query to fetch one record and see the details of the query using the EXPLAIN clause.

explain select productname, productline,buyprice, msrp from products where productname="pont yacht";Code language: JavaScript (javascript)

Here, we are finding the data of the product having the name ‘pont yacht’.

Let’s see the information about the query.

Now let’s create a prefix index on the productname column and execute the same query again.

But before that, how will you know the prefix length? For doing this, you can find the maximum count of unique data from the table using the following way-

select count(distinct(left(productname,7))) from products;
select count(distinct(left(productname,8))) from products;Code language: SQL (Structured Query Language) (sql)

Here, the length 8 gives us 101 distinct records, so it is a good length to be set as a prefix length.

CREATE INDEX prefixIndexProdName ON products(productname(9));Code language: SQL (Structured Query Language) (sql)

The index is created successfully.

Let’s write the above query to search for the product by its name.

explain select productname, productline,buyprice, msrp from products where productname="pont yacht";Code language: SQL (Structured Query Language) (sql)

As you can see in the result, the query uses the prefix index that we have created recently and of course, the query is much faster than before.

Summary

In this tutorial, we have learned about the MySQL prefix index. It is a little bit tricky to understand but easy to implement and use. However, it is very less likely recommended to use in live projects because you won’t know how much data is going to be there which is unique. However, it is good to know information about it as it might help in some cases.

References

Stackoverflow thread on MySQL prefix index vs index.