CREATE INDEX Statement If you specify an index type that is not valid for a given storage engine, but another index type is available that the engine can use without affecting query results, the engine uses the available type. The parser recognizes  RTREE  as a type name, but currently this cannot be specified for any storage engine. For unique indexes that include one or more  NULL  columns of an  NDB  table, the hash index can be used only to look up literal values, which means that  IS [NOT] NULL  conditions require a full scan of the table. One workaround is to make sure that a unique index using one or more  NULL  columns on such a table is always created in such a way that it includes the ordered index; that is, avoid employing  USING HASH  when creating the index. Use of the  index_type  option before the  ON  tbl_name  clause is deprecated; expect support for use of the option in this position to be removed in a future MySQL release. If an  index_type  option is given in both the earlier and later positions, the final option applies. Note TYPE  type_name  is recognized as a synonym for  USING  type_name . However,  USING  is the preferred form. The following tables show index characteristics for the storage engines that support the  index_type option. Table 13.2 InnoDB Storage Engine Index Characteristics Index Class Index Type Stores NULL VALUES Permits Multiple NULL Values IS NULL Scan Type IS NOT NULL Scan Type Primary key BTREE No No N/A N/A Unique BTREE Yes Yes Index Index Key BTREE Yes Yes Index Index FULLTEXT N/A Yes Yes Table Table SPATIAL N/A No No N/A N/A Table 13.3 MyISAM Storage Engine Index Characteristics Index Class Index Type Stores NULL VALUES Permits Multiple NULL Values IS NULL Scan Type IS NOT NULL Scan Type Primary key BTREE No No N/A N/A Unique BTREE Yes Yes Index Index Key BTREE Yes Yes Index Index FULLTEXT N/A Yes Yes Table Table 2526