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 | | |
+-------+------------+----------+--------------+---------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
A table can contain a maximum of 64 secondary indexes.
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.
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.
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.
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
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With