You can add an index to a column that can has NULL values if you are using the MyISAM, InnoDB, or MEMORY storage engine.

W

Https://dev.mysql.com/doc/refman/5.7/en/create-index.html

MySQL:: MySQL 5.7 Reference Manual:: b.5.4.3 problems with NULL Values
Https://dev.mysql.com/doc/refman/5.7/en/problems-with-null.html

You can add an index to a column that can has NULL the values if you are using MyISAM the, InnoDB or MEMORY storage engine. Otherwise, you must declare a indexed column NOT NULL , and you cannot insert into the NULL column.

MySQL:: MySQL 5.7 Reference Manual:: 14.1.14 CREATE INDEX Syntax
Https://dev.mysql.com/doc/refman/5.7/en/create-index.html

The,, MyISAM InnoDB NDB , and ARCHIVE storage engines support spatial columns such as ( POINT and GEOMETRY . (Section 12.5, "Extensions for Spatial Data", describes the spatial data types.) However, support for spatial column indexing varies among engines. Spatial and Nonspatial indexes is available according to the following rules.

Spatial indexes (created using SPATIAL INDEX ) has these characteristics:

• Available only for MyISAM and InnoDB tables. Specifying SPATIAL INDEX -Storage engines results in an error.

• Indexed columns must be NOT NULL .

• Column prefix lengths is prohibited. The full width of each column is indexed.

Characteristics of Nonspatial indexes (created INDEX with, UNIQUE , or PRIMARY KEY ):

• Permitted for any storage engine that supports spatial columns except ARCHIVE .

• Columns can NULL unless the index is a primary key.

• For each of the spatial column in a non- SPATIAL the index except POINT columns, a column prefix length must be specified. (This was the same requirement as for indexed BLOB columns.) The prefix length is given in bytes.

• The index type for a non- SPATIAL index depends on the storage engine. Currently, B-tree is used.

• You can add an index to a column NULL that can has the values only InnoDB for, MyISAM , and MEMORY tables.

• You can add an index to a BLOB or column only for using the and TEXT InnoDB MyISAM tables.

• When innodb_stats_persistent the setting was enabled, run the statement for a table after creating a index on that ANALYZE TABLE InnoDB table.

You can add an index to a column that can has NULL values if you are using the MyISAM, InnoDB, or MEMORY storage engine.