Affects: Server-7.1   —   Status: Un-Assigned   —   Priority: Medium


About 20 users in BUG#4541 wanted this feature.


Add support for unique blob/text checking.

This should be done this way:

Use the following (already existing) key definition syntax:

CREATE TABLE ( ..., UNIQUE index_name (col1, col2, ...) USING HASH)

where any of the columns could be a blob. Note that this optimization is also
very useful for CHAR/VARCHAR columns as the generated hash index is much 
smaller than if we would save the whole key.  It will also allow the user to do 
constraint on things that are longer than MyISAM MAX_KEY_LENGTH.

In this case we should not create a key for 'index_name' but instead a
unique check, if the handler support this.
(If the handler doesn't support this, we should create a normal key
for this, if possible.)

From the user's point of view, the main differences between UNIQUE and UNIQUE 
... USING HASH are:
- UNIQUE ... USING HASH should take much less disk space.
- With an UNIQUE ... USING HASH you can only use the index when searching on the 
whole key (no prefix key searches) and only with =.

NDB can use UNIQUE ... USING HASH syntax to only create a HASH key on the index 
instead of an ordered index. In this case, any keys that contains NULL will be 

Part of this task is to do:

- Check that this works properly with keys that have NULL in them.
  (When checking for duplicates keys with NULL should be regarded as
- Get this to work with ALTER TABLE and CREATE TABLE
- SHOW CREATE TABLE should show the USING HASH keyword.
- There is not much that has to be changed on the MySQL side to
  support this but one has to do some changes to MyISAM to enable one
  to call mi_rkey() on a hash key.
- We need to add a handler specific flag per key to inform the optimzier
  if the handler will stores null in the hash key or not. If not, we have to 
use table scans if the users has (or can have) a NULL value in the key.

Some additional information about MyISAM:
- The unique handling code (that is now mainly used for things like SELECT 
DISTINCT when we want to make the whole row unique) is in 

MyISAM can already create unique constraints, with the 'MI_UNIQUEDEF
*' parameter to mi_create()'.

don't forget to close BUG#4541 when this task is implemented

It's undecided whether foreign keys can reference unique text/blob columns.