Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

Max size of UNIQUE index in MySQL

Tags:

mysql

Is there a max size of a UNIQUE index in MySQL? I have six dimensions in my index, which leads to a massive index. Is there a cardinality limit to UNIQUE index?

I am getting weird unexplained errors and wonder if there is a limit from the UNIQUE. Could it come from a uniqueness limit? Here is what it looks like (I also don't understand why the cardinality of the last three columns does not increase).

+-------+------------+----------+--------------+---------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| Table | Non_unique | Key_name | Seq_in_index | Column_name   | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment |
+-------+------------+----------+--------------+---------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| table |          0 | PRIMARY  |            1 | column_1      | A         |          15 |     NULL | NULL   |      | BTREE      |         |               |
| table |          0 | PRIMARY  |            2 | column_2      | A         |       91948 |     NULL | NULL   |      | BTREE      |         |               |
| table |          0 | PRIMARY  |            3 | column_3      | A         |      924889 |     NULL | NULL   |      | BTREE      |         |               |
| table |          0 | PRIMARY  |            4 | column_4      | A         |    15723114 |     NULL | NULL   |      | BTREE      |         |               |
| table |          0 | PRIMARY  |            5 | column_5      | A         |    15723114 |     NULL | NULL   |      | BTREE      |         |               |
| table |          0 | PRIMARY  |            6 | column_6      | A         |    15723114 |     NULL | NULL   |      | BTREE      |         |               |
+-------+------------+----------+--------------+---------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
like image 547
Mad Echet Avatar asked May 15 '13 14:05

Mad Echet


People also ask

What is the maximum number of index per table in MySQL?

A table can contain a maximum of 64 secondary indexes.

What is unique index in MySQL?

Indexing is a process to find an unordered list into an ordered list that allows us to retrieve records faster. It creates an entry for each value that appears in the index columns. It helps in maximizing the query's efficiency while searching on tables in MySQL.

What is index length in MySQL?

The index key prefix length limit is 767 bytes for InnoDB tables that use the REDUNDANT or COMPACT row format. For example, you might hit this limit with a column prefix index of more than 191 characters on a TEXT or VARCHAR column, assuming a utf8mb4 character set and the maximum of 4 bytes for each character.

What qualifies as a unique index?

Unique indexes are indexes that help maintain data integrity by ensuring that no rows of data in a table have identical key values. When you create a unique index for an existing table with data, values in the columns or expressions that comprise the index key are checked for uniqueness.


1 Answers

For InnoDB tables, the limit is 3072 bytes across all indexed columns, presumably taking only the first 767 bytes of each column.

An index key for a single-column index can be up to 767 bytes. The same length limit applies to any index key prefix. See Section 13.1.13, “CREATE INDEX Syntax”.

The InnoDB internal maximum key length is 3500 bytes, but MySQL itself restricts this to 3072 bytes. This limit applies to the length of the combined index key in a multi-column index.

http://dev.mysql.com/doc/refman/5.1/en/innodb-restrictions.html

So, based strictly on the documentation, I'd say you could have up to 1000 columns in your index (the max number of columns on an InnoDB table), provided that the average size of each is 3 bytes or less.

For MyISAM tables, it's the lesser of 16 columns or 1000 bytes.

The maximum number of columns per index is 16.

The maximum key length is 1000 bytes. This can also be changed by changing the source and recompiling. For the case of a key longer than 250 bytes, a larger key block size than the default of 1024 bytes is used.

http://dev.mysql.com/doc/refman/5.0/en/myisam-storage-engine.html

like image 60
svidgen Avatar answered Nov 02 '22 23:11

svidgen