Factor in DB Nulls filters When Creating Indexes
A NULL is a special significance in a relational database that indicates “unknown” or the lack of information in a column. A null can never be equal to or not equal to some other value, not another null. To detect the existence of a null value, you must use the specific “IS NULL/IS NOT NULL” operator.
MySQL doesn’t permit null values in primary important columns. It will, nevertheless, consider null values to be unequal when creating an index, so it is permissible to store multiple rows having a column with a null value and a UNIQUE index on that column. MySQL indexes null values, and there’s absolutely no choice to get rid of them.
Things to Remember
- Consider whether a DB column that you need to index will contain null values.
- If You Would like to search for null values, but Nearly All values in the column are going to be NULLs, it is recommended not to index the column. It may be also a sign that redesign the table could be warranted.
- If You Want to Have the Ability to search for worth on a column quickly, but the majority
Of the values will be NULL, construct the index with no null values if your database supports it.
Ex: For example, MySQL can use indexes and ranges to search for NULL with IS NULL.
SELECT * FROM table1 WHERE key_col IS NULL; SELECT * FROM table1 WHERE key_col <=> NULL; SELECT * FROM table1 WHERE key_col=const1 OR key_col=const2 OR key_col IS NULL;
the SmartMySQL is the best MySQL query Optimization tool and well aware when the index for IS NULL filters and when not.
