MySQL Bugs: #67771: Index documentation incomplete

Bug #67771	Index documentation incomplete
Submitted:	30 Nov 2012 17:37	Modified:	25 Feb 2016 16:29
Reporter:	Kevin Benton	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Server: Documentation	Severity:	S3 (Non-critical)
Version:	Any	OS:	Any
Assigned to:	Paul DuBois	CPU Architecture:	Any

Description:
http://dev.mysql.com/doc/refman/5.5/en/create-index.html shows the following:

* index_type

Some storage engines permit you to specify an index type when creating an index. The permissible index type values supported by different storage engines are shown in the following table. Where multiple index types are listed, the first one is the default when no index type specifier is given.

Storage Engine	Permissible Index Types
MyISAM	BTREE
InnoDB	BTREE
MEMORY/HEAP	HASH, BTREE
NDB	BTREE, HASH (see note in text)

The problem here is that the list of storage engines is far from complete. Missing are at least the Archive, CSV, and others. Spatial doesn't appear either.

Also - on the same page, there is no classification name for Primary/Unique/(Plain)/Spatial/Fulltext. Why not choose "Index Class" as the label for those values?

Finally, I'm working with our TAM to create a matrix of NULL value handling by storage engine, index class and type. Across the top of that matrix, we have 
Engine
Index Type
Index Class
Stores NULL Values
Allows Multiple NULL Values
IS NULL Scan Type
IS NOT NULL Scan Type

On the left, each of the storage engines, index classes and types is specified followed by the appropriate values from the top.

How to repeat:
See description.

Suggested fix:
See description.

Thank you for the report.

> * index_type
...
> The problem here is that the list of storage engines is far from complete. Missing are at least the Archive, CSV, and others.

But nor Archive, neither CSV support index_type. CSV does not support indexes either:

mysql [localhost] {msandbox} (test) > CREATE TABLE lookup (id INT not null) ENGINE =archive;
Query OK, 0 rows affected (0.12 sec)

mysql [localhost] {msandbox} (test) > CREATE INDEX id_index ON lookup (id) USING BTREE;
ERROR 1005 (HY000): Can't create table 'test.#sql-54f9_1' (errno: -1)
mysql [localhost] {msandbox} (test) > drop table lookup;
Query OK, 0 rows affected (0.00 sec)

mysql [localhost] {msandbox} (test) > CREATE TABLE lookup (id INT not null) ENGINE =csv;
Query OK, 0 rows affected (0.14 sec)

mysql [localhost] {msandbox} (test) > CREATE INDEX id_index ON lookup (id) USING BTREE;
ERROR 1069 (42000): Too many keys specified; max 0 keys allowed

> Spatial doesn't appear either.

Spatial is not index type in this context. Spatial is more likely kind of index, like UNIQUE, FULLTEXT, etc.

Also, few strings below, there is a text: "The index_type clause cannot be used together with SPATIAL INDEX."

> Also - on the same page, there is no classification name for Primary/Unique/(Plain)/Spatial/Fulltext. Why not choose "Index Class" as the label for those values?

Here it is: 

A UNIQUE index creates a constraint such that all values in the index must be distinct. An 
...
Spatial indexes (created using SPATIAL INDEX) have these characteristics: 
...
Characteristics of nonspatial indexes (created with INDEX, UNIQUE, or PRIMARY KEY):
...

You can find more.

So this looks like our manual is correct for me. Do you still have any issues with this page?

As mentioned in SR 3-6507623741:

"The point in making this request was to have the information in a matrix all on a single page to help DBAs and DBDs make effective decisions when comparing capabilities of each of the storage engines. Such a matrix should make it plainly obvious which index types are usable with each of the storage engines along with each of the index classes. As described to our TAM, there are cases when we need just that very documentation and why we've asked our TAM specifically to compile it for us. Once built, I see no reason to just keep it to ourselves, but rather to ask that the documentation be updated to include the same information [ed. hence the reason for this bug.].

"The page that includes this matrix would also be a fantastic place to explain the different index types and benefits/drawbacks of each type.

"While I understand that Oracle is working hard at trying to make the other storage engines more or less obsolete, we aren't there yet and there are still times when other storage engines have enough performance and storage utilization benefits to actually use those other engines."

My reason for calling the documentation incomplete is the lack of the matrix making it much easier to determine which storage engine supports each type of index and to include that matrix on the appropriate pages (like the CREATE INDEX and/or ALTER TABLE CREATE INDEX syntax pages).

Thank you for the feedback.

Sounds reasonable. I set this to "Verified", so our Docs team can implement this.

If you have material you want to publish, feel free to send it to us. You should sign OCA agreement in this case.

Thank you for your bug report. This issue has been addressed in the documentation. The updated documentation will appear on our website shortly.