[MDEV-5795] Full-Column Unique Index Created: 2014-03-05  Updated: 2014-03-06  Resolved: 2014-03-05

Status: Closed
Project: MariaDB Server
Component/s: None
Fix Version/s: None

Type: Task Priority: Major
Reporter: BELUGABEHR Assignee: Unassigned
Resolution: Duplicate Votes: 0
Labels: None

Issue Links:
PartOf
is part of MDEV-371 Unique indexes for blobs Closed

 Description   

From the MySQL Manual:

A UNIQUE index creates a constraint such that all values in the index must be distinct. An error occurs if you try to add a new row with a key value that matches an existing row. For all engines, a UNIQUE index permits multiple NULL values for columns that can contain NULL. If you specify a prefix value for a column in a UNIQUE index, the column values must be unique within the prefix.

Can you please implement a way to specify that uniqueness should extend beyond the prefix and apply to the entire column? Obviously the index could be used as a start, but any rows with matching prefix should be compared against the entire value.

1) Search the index
2) Compare the VARCHAR length of any matching prefix columns
3) Compare the actual values

Sure it would be a bit slower than index-only, but grant us the flexibility to decide full comparisons v.s. index and remove this limitation.

Some practical examples:
SOF - Best primary key for storing URLs
SOF - Most efficient way to store URLs in Mysql?
SE - MySQL: Storing unique URLs

Thanks!

 Comments   
Comment by Sergei Golubchik [ 2014-03-05 ]

You can have an index over the whole VARCHAR column if you don't specify the prefix length.
For BLOB/TEXT columns you must specify the prefix that's true. Removing this limitation is MDEV-371

Comment by BELUGABEHR [ 2014-03-06 ]

By default, 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.11, “CREATE INDEX Syntax”. For example, you might hit this limit with a column prefix index of more than 255 characters on a TEXT or VARCHAR column, assuming a UTF-8 character set and the maximum of 3 bytes for each character.

So it is not so simple to say that "You can have an index over the whole VARCHAR column." Again, I refer to the examples I posted. Uniqueness cannot be applied to long URLs in VARCHAR fields because of this limitation. I would simply suggest that the implementation for enforcing uniqueness be definite. The solution that I posted could be one way. Use a prefix index for a given storage engine and when collisions do occur in the index, do a serial search for a literal match.

Comment by Sergei Golubchik [ 2014-03-06 ]

You're right. The limitation of 767 bytes applies to indexes over VARCHAR too.
But as I said above, MDEV-371 will remove this limitation, one will be able to have UNIQUE indexes over arbitrary long sets of columns.

Generated at Thu Feb 08 07:07:06 UTC 2024 using Jira 8.20.16#820016-sha1:9d11dbea5f4be3d4cc21f03a88dd11d8c8687422.