Index for Nullable Column

Index for nullable column

By default, relational databases ignore NULL values (because the relational model says that NULL means "not present"). So, Index does not store NULL value, consequently if you have null condition in SQL statement, related index is ignored (by default).

But you can suprass this problem, check THIS or THIS article.

Can we create an index on a column containing NULL values in MySQL 5.7?

A PRIMARY key is used to organize the data on disk and because there is a relationship to how the data is physically arranged you cannot have any part of a primary key being NULL.

A non-primary index CAN have one or more columns that allow NULLs. demo

CREATE TABLE `my_docs` (
  `id` int(6) unsigned NOT NULL AUTO_INCREMENT,
  `rev` int(3) ,
  `content` varchar(200) NOT NULL,
  PRIMARY KEY (`id`)
) DEFAULT CHARSET=utf8;

INSERT INTO `my_docs` (`rev`, `content`) VALUES
  (1, 'The earth is flat'),
  (1, 'One hundred angels can dance on the head of a pin'),
  (NULL, 'The earth is flat and rests on a bull\'s horn'),
  (3, 'The earth is like a ball.');

alter table `my_docs` add key my_added_index (`rev`);

SELECT id, rev, content
FROM `my_docs`
where rev = 3

| id | rev |                   content |
|----|-----|---------------------------|
|  4 |   3 | The earth is like a ball. |

+---+-------------+-----------+------+----------------+----------------+---------+-------+------+----------+-------+
| d | select_type |   table   | type | possible_keys  |      key       | key_len |  ref  | rows | filtered | Extra |
+---+-------------+-----------+------+----------------+----------------+---------+-------+------+----------+-------+
| 1 |    SIMPLE   |   my_docs | ref  | my_added_index | my_added_index |       5 | const |    1 |   100.00 |       |
+---+-------------+-----------+------+----------------+----------------+---------+-------+------+----------+-------+

Non clustered index not used for nullable column

This is because your nonclustered index covers predicates for "EntityId" and "AggregateEntityId" or only "EntityId" based on the order that the columns are listed on the index.

This is because the B-Tree that stores the nonclustered index data first sorts on EntityId then by AggregateEntityId.

Since your second query doesn't use EntityId in the WHERE clause, the nonclustered index is invalid in this case because it's unable to efficiently locate AggregateEntityId without being able to first filter on EntityId.

You would need another nonclustered index that starts with AggregateEntityId (or only contains AggregateEntityId) under the Indexed Columns.

Does SQL Server Index Null Values in a Non-Clustered Non-Unique index?

I would suggest a filtered index, such as WHERE column IS NOT NULL; - this will allow you to create an index that only bothers to index the non-NULL values, and ignores all of the rows with no value. You'll probably want to make sure the index covers the queries you want to run with this type of predicate, so that you don't have to go back into the whole table to lookup the other columns the query needs to output (or use in a join, or otherwise filter, etc).

More details here.

Does PostgreSQL index null values?

NULL values ARE indexed as well.

You can also use an index to speed up queries with a condition like

WHERE col IS NULL

Something that may come as a surprise to Oracle users is that you can have several rows with a NULL in a unique index. But that makes sense because NULL = NULL is not true.

How to create a unique index on a NULL column?

Pretty sure you can't do that, as it violates the purpose of uniques.

However, this person seems to have a decent work around:
http://sqlservercodebook.blogspot.com/2008/04/multiple-null-values-in-unique-index-in.html

If my database column has a lot of null values, why shouldn't I use an index on it?

Indexes on columns with lots of NULL values can be useful, particularly if your query is looking at the values that are populated.

For your example, though, you have a different issue. In general, indexes on binary columns are not useful. The purpose of indexes is to reduce the number of data pages that need to be read. In general, binary columns are going to have records with both values on any given data page.

There are two exceptions, but the second doesn't apply to Postgres. The first is if one of the values is so rare that the values occur on few data pages. Then an index can be helpful to find them.

The second is for a clustered index. Because Postgres does not support them, that exception is not relevant.

---

Related Topics