MySQL Prefix Index

Summary: in this tutorial, you will learn how to use MySQL prefix index to create indexes for character string columns.

Introduction to MySQL Prefix Index

When you create a secondary index for a column, MySQL stores the values of the columns in a separate data structure e.g., B-Tree and Hash.

In case the columns are string columns, the index will consume a lot of disk space and potentially slow down the INSERT operations.

To address this issue, MySQL allows you to create an index for the leading part of the column values of the string columns using the following syntax:

column_name(length)Code language: SQL (Structured Query Language) (sql)

For example, the following statement creates the column prefix key part at the time of table creation:

CREATE TABLE table_name(
    column_list,
    INDEX(column_name(length))
);Code language: SQL (Structured Query Language) (sql)

Or add an index to an existing table:

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

In this syntax, the length is the number of characters for the non-binary string types such as CHAR, VARCHAR, and TEXT and the number of bytes for binary string types e.g., BINARY, VARBINARY, and BLOB.

MySQL allows you to optionally create column prefix key parts for CHAR, VARCHAR, BINARY, and VARBINARY columns. If you create indexes for BLOB and TEXT columns, you must specify the column prefix key parts.

Notice that the prefix support and lengths of prefixes if supported are storage engine dependent. For InnoDB tables with  REDUNDANT or COMPACT row format, the maximum prefix length is 767 bytes. However, for the InnoDB tables with  DYNAMIC or COMPRESSED row format, the prefix length is 3,072 bytes. MyISAM tables have a prefix length of up to 1,000 bytes.

MySQL prefix index example

We will use the products table from the sample database for the demonstration.

The following query finds the products whose names start with the string 1970:

SELECT 
    productName, 
    buyPrice, 
    msrp
FROM
    products
WHERE
    productName LIKE '1970%';Code language: SQL (Structured Query Language) (sql)

Because there is no index for the  productName column, the query optimizer has to scan all rows to return the result as shown in the output of the EXPLAIN statement below:

EXPLAIN SELECT 
    productName, 
    buyPrice, 
    msrp
FROM
    products
WHERE
    productName LIKE '1970%';Code language: SQL (Structured Query Language) (sql)

Here is the output:

If you often find the products by the product name, then you should create an index for this column because it will be more efficient for searches.

The size of the product name column is 70 characters. We can use the column prefix key parts.

The next question is how do you choose the length of the prefix? To do this, you can investigate the existing data. The goal is to maximize the uniqueness of the values in the column when you use the prefix.

To do this, you follow these steps:

Step 1. Find the number of rows in the table:

SELECT
   COUNT(*)
FROM
   products;Code language: SQL (Structured Query Language) (sql)

Step2. Evaluate different prefix lengths until you can achieve the reasonable uniqueness of rows:

SELECT
   COUNT(DISTINCT LEFT(productName, 20)) unique_rows
FROM
   products;Code language: SQL (Structured Query Language) (sql)

As shown in the output, 20 is a good prefix length in this case because if we use the first 20 characters of the product name for the index, all product names are unique.

Let’s create an index with the prefix length 20 for the productName column:

CREATE INDEX idx_productname 
ON products(productName(20));Code language: SQL (Structured Query Language) (sql)

And execute the query that finds products whose name starts with the string 1970 again:

EXPLAIN SELECT 
    productName, 
    buyPrice, 
    msrp
FROM
    products
WHERE
    productName LIKE '1970%';
Code language: SQL (Structured Query Language) (sql)

Now, the query optimizer uses the newly created index which is much faster and more efficient than before.

In this tutorial, you have learned how to use the MySQL prefix index to create indexes for string columns.