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 the 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:

1	column_name(length)

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

1 2 3 4

CREATE TABLE table_name(     column_list,     INDEX(column_name(length)) );

Or add an index to an existing table:

1 2	CREATE INDEX index_name ON table_name(column_name(length));

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 the prefix length 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:

1 2 3 4 5 6 7 8

SELECT     productName,     buyPrice,     msrp FROM     products WHERE     productName LIKE '1970%';

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:

1 2 3 4 5 6 7 8

EXPLAIN SELECT     productName,     buyPrice,     msrp FROM     products WHERE     productName LIKE '1970%';

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? For doing 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:

1 2 3 4

SELECT    COUNT(*) FROM    products;

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

1 2 3 4

SELECT    COUNT(DISTINCT LEFT(productName, 20)) unique_rows FROM    products;

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:

1 2	CREATE INDEX idx_productname ON products(productName(20));

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

1 2 3 4 5 6 7 8

EXPLAIN SELECT     productName,     buyPrice,     msrp FROM     products WHERE     productName LIKE '1970%';

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.