Bug #77823 Clarify use of NULL unique indexes in Cluster
Description:
Currently the manual says the following about unique indexes in MySQL Cluster:
"
Note
For indexes on NDB table columns, the USING option can be specified only for a unique index or primary key. USING HASH prevents the creation of an implicit ordered index; otherwise, creating a unique index or primary key on an NDB table automatically results in the creation of both an ordered index and a hash index, each of which indexes the same set of columns.
This means that a query using a unique index or primary key on a NULL column is always handled by NDB with a full scan of the table. In particular, if you plan to use an IS NULL or IS NOT NULL condition involving a unique index or primary key column of an NDB table, you should create any such index without USING HASH.
"
However there are some problems with this:
* Indexes lookups will be used for NULL unique indexes in some cases. Assume the column val allows NULL values and has a unique index, then:
- val = 'abc' will use an index in all cases
- val IS NOT NULL can use an ordered index
- val IS NULL can use an ordered index
* "This means that a query using a unique index or primary key on a NULL column" - a primary key can never accept NULL values; it must always be NOT NULL.
How to repeat:
See http://dev.mysql.com/doc/refman/5.6/en/create-index.html
Suggested fix:
This will be a better formulation - or similar:
"
For indexes on NDB table columns, the USING option can be specified only for a unique index or primary key. USING HASH prevents the creation of an ordered index; otherwise, creating a unique index or primary key on an NDB table automatically results in the creation of both an ordered index and a hash index, each of which indexes the same set of columns.
For unique indexes that includes one or more NULL columns, the hash index can only be used to look up literals, i.e. IS NOT NULL and IS NULL conditions require a full table scan. One workaround is to ensure unique indexes with NULL columns are created so they include the ordered index.
"
Portions of this website are copyright © 2001, 2002 The PHP Group
Page generated in 0.038 sec. using MySQL 8.4.7-u6-cloud
Timestamp references displayed by the system are UTC.
Content reproduced on this site is the property of the
respective copyright holders.
It is not reviewed in advance
by Oracle and does not necessarily represent the opinion of
Oracle or any other party.
Description: Currently the manual says the following about unique indexes in MySQL Cluster: " Note For indexes on NDB table columns, the USING option can be specified only for a unique index or primary key. USING HASH prevents the creation of an implicit ordered index; otherwise, creating a unique index or primary key on an NDB table automatically results in the creation of both an ordered index and a hash index, each of which indexes the same set of columns. This means that a query using a unique index or primary key on a NULL column is always handled by NDB with a full scan of the table. In particular, if you plan to use an IS NULL or IS NOT NULL condition involving a unique index or primary key column of an NDB table, you should create any such index without USING HASH. " However there are some problems with this: * Indexes lookups will be used for NULL unique indexes in some cases. Assume the column val allows NULL values and has a unique index, then: - val = 'abc' will use an index in all cases - val IS NOT NULL can use an ordered index - val IS NULL can use an ordered index * "This means that a query using a unique index or primary key on a NULL column" - a primary key can never accept NULL values; it must always be NOT NULL. How to repeat: See http://dev.mysql.com/doc/refman/5.6/en/create-index.html Suggested fix: This will be a better formulation - or similar: " For indexes on NDB table columns, the USING option can be specified only for a unique index or primary key. USING HASH prevents the creation of an ordered index; otherwise, creating a unique index or primary key on an NDB table automatically results in the creation of both an ordered index and a hash index, each of which indexes the same set of columns. For unique indexes that includes one or more NULL columns, the hash index can only be used to look up literals, i.e. IS NOT NULL and IS NULL conditions require a full table scan. One workaround is to ensure unique indexes with NULL columns are created so they include the ordered index. "