{"id":6911,"date":"2018-08-21T05:32:26","date_gmt":"2018-08-21T12:32:26","guid":{"rendered":"http:\/\/www.mysqltutorial.org\/?page_id=6911"},"modified":"2023-10-06T22:06:50","modified_gmt":"2023-10-07T05:06:50","slug":"mysql-prefix-index","status":"publish","type":"page","link":"https:\/\/www.mysqltutorial.org\/mysql-index\/mysql-prefix-index\/","title":{"rendered":"MySQL Prefix Index"},"content":{"rendered":"\n

Summary<\/strong>: in this tutorial, you will learn how to use MySQL prefix index to create indexes for character string columns.<\/p>\n\n\n\n

Introduction to MySQL Prefix Index<\/h2>\n\n\n\n

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

In case the columns are string columns, the index will consume a lot of disk space and potentially slow down the INSERT<\/a><\/code> operations.<\/p>\n\n\n\n

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:<\/p>\n\n\n

column_name(length)<\/code><\/span>Code language:<\/span> SQL (Structured Query Language)<\/span> (<\/span>sql<\/span>)<\/span><\/small><\/pre>\n\n\n
For example, the following statement creates the column prefix key part at the time of table creation:<\/p>\n\n\n
CREATE<\/span> TABLE<\/span> table_name(\n    column_list,\n    INDEX<\/span>(column_name(length<\/span>))\n);<\/code><\/span>Code language:<\/span> SQL (Structured Query Language)<\/span> (<\/span>sql<\/span>)<\/span><\/small><\/pre>\n\n\n
Or add an index to an existing table:<\/p>\n\n\n
CREATE<\/span> INDEX<\/span> index_name\nON<\/span> table_name(column_name(length<\/span>));\n<\/code><\/span>Code language:<\/span> SQL (Structured Query Language)<\/span> (<\/span>sql<\/span>)<\/span><\/small><\/pre>\n\n\n
MySQL allows you to optionally create column prefix key parts for CHAR<\/code>, VARCHAR<\/code>, BINARY<\/code>, and VARBINARY<\/code> columns. If you create indexes for BLOB<\/code> and TEXT<\/code> columns, you must specify the column prefix key parts.<\/p>\n\n\n\n
Notice that the prefix support and lengths of prefixes if supported are storage engine<\/a> dependent. For InnoDB tables with\u00a0 REDUNDANT<\/code> or COMPACT<\/code> row format, the maximum prefix length is 767 bytes. However, for the InnoDB tables with\u00a0 DYNAMIC<\/code> or COMPRESSED<\/code> row format, the prefix length is 3,072 bytes. MyISAM tables have a prefix length of up to 1,000 bytes.<\/p>\n\n\n\n
MySQL prefix index example<\/h2>\n\n\n\n
We will use the products<\/code> table from the sample database<\/a> for the demonstration.<\/p>\n\n\n\n
\"products<\/figure>\n\n\n\n
The following query finds the products whose names start with the string 1970:<\/p>\n\n\n
SELECT<\/span> \n    productName, \n    buyPrice, \n    msrp\nFROM<\/span>\n    products\nWHERE<\/span>\n    productName LIKE<\/span> '1970%'<\/span>;<\/code><\/span>Code language:<\/span> SQL (Structured Query Language)<\/span> (<\/span>sql<\/span>)<\/span><\/small><\/pre>\n\n\n
Because there is no index for the  productName<\/code> column, the query optimizer has to scan all rows to return the result as shown in the output of the EXPLAIN<\/code> statement below:<\/p>\n\n\n
EXPLAIN<\/span> SELECT<\/span> \n    productName, \n    buyPrice, \n    msrp\nFROM<\/span>\n    products\nWHERE<\/span>\n    productName LIKE<\/span> '1970%'<\/span>;<\/code><\/span>Code language:<\/span> SQL (Structured Query Language)<\/span> (<\/span>sql<\/span>)<\/span><\/small><\/pre>\n\n\n
Here is the output:<\/p>\n\n\n\n
\"MySQL<\/figure>\n\n\n\n
If you often find the products by the product name, then you should create an index<\/a> for this column because it will be more efficient for searches.<\/p>\n\n\n\n
The size of the product name column is 70 characters. We can use the column prefix key parts.<\/p>\n\n\n\n
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.<\/p>\n\n\n\n
To do this, you follow these steps:<\/p>\n\n\n\n
Step 1. Find the number of rows in the table:<\/p>\n\n\n
SELECT<\/span>\n   COUNT<\/span>(*)\nFROM<\/span>\n   products;<\/code><\/span>Code language:<\/span> SQL (Structured Query Language)<\/span> (<\/span>sql<\/span>)<\/span><\/small><\/pre>\n\n\n
Step2. Evaluate different prefix lengths until you can achieve the reasonable uniqueness of rows:<\/p>\n\n\n
SELECT<\/span>\n   COUNT<\/span>(DISTINCT<\/span> LEFT<\/span>(productName, 20<\/span>)) unique_rows\nFROM<\/span>\n   products;<\/code><\/span>Code language:<\/span> SQL (Structured Query Language)<\/span> (<\/span>sql<\/span>)<\/span><\/small><\/pre>\n\n\n
\"MySQL<\/figure>\n\n\n\n
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.<\/p>\n\n\n\n
Let’s create an index with the prefix length 20 for the productName<\/code> column:<\/p>\n\n\n
CREATE<\/span> INDEX<\/span> idx_productname \nON<\/span> products(productName(20<\/span>));<\/code><\/span>Code language:<\/span> SQL (Structured Query Language)<\/span> (<\/span>sql<\/span>)<\/span><\/small><\/pre>\n\n\n
And execute the query that finds products whose name starts with the string 1970 again:<\/p>\n\n\n
EXPLAIN<\/span> SELECT<\/span> \n    productName, \n    buyPrice, \n    msrp\nFROM<\/span>\n    products\nWHERE<\/span>\n    productName LIKE<\/span> '1970%'<\/span>;\n<\/code><\/span>Code language:<\/span> SQL (Structured Query Language)<\/span> (<\/span>sql<\/span>)<\/span><\/small><\/pre>\n\n\n
\"MySQL<\/figure>\n\n\n\n
Now, the query optimizer uses the newly created index which is much faster and more efficient than before.<\/p>\n\n\n\n
In this tutorial, you have learned how to use the MySQL prefix index to create indexes for string columns.<\/p>\n
\n\t
\n\t\t
Was this tutorial helpful? <\/div>\n\t\t
\n\t\t\t\n\t\t\t\t\n\t\t\t\t\t<\/path>\n\t\t\t\t<\/svg>\n\t\t\t\t Yes <\/span>\n\t\t\t<\/button>\n\n\t\t\t\n\t\t\t\t\n\t\t\t\t\t<\/path>\n\t\t\t\t<\/svg>\n\t\t\t\t No <\/span>\n\t\t\t<\/button>\n\t\t<\/div>\n\t<\/header>\n\n\t
\n\t\t
\n\t\t\t
<\/div>\n\t\t\t\n\t\t\t
In this syntax, the length is the number of characters for the non-binary string types such as CHAR<\/a><\/code>, VARCHAR<\/a><\/code>, and TEXT<\/a><\/code> and the number of bytes for binary string types e.g., BINARY<\/code>, VARBINARY<\/code>, and BLOB<\/code>.<\/p>\n\n\n\n