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.
"
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. "