81

I have a MySQL table where an indexed INT column is going to be 0 for 90% of the rows. If I change those rows to use NULL instead of 0, will they be left out of the index, making the index about 90% smaller?

Yves M.
  • 29,855
  • 23
  • 108
  • 144
too much php
  • 88,666
  • 34
  • 128
  • 138

5 Answers5

36

http://dev.mysql.com/doc/refman/5.0/en/is-null-optimization.html

MySQL can perform the same optimization on col_name IS NULL that it can use for col_name = constant_value. For example, MySQL can use indexes and ranges to search for NULL with IS NULL.

Yves M.
  • 29,855
  • 23
  • 108
  • 144
Khanh Van
  • 1,422
  • 11
  • 6
  • 4
    Please note that the documentation mentions some caveats, e.g. "the optimization can handle only one IS NULL". – Timo Nov 29 '16 at 09:17
11

It looks like it does index the NULLs too.

Be careful when you run this because MySQL will LOCK the table for WRITES during the index creation. Building the index can take a while on large tables even if the column is empty (all nulls).

Reference.

Bill the Lizard
  • 398,270
  • 210
  • 566
  • 880
  • 2
    How did you come to that conclusion? I don't see any mention of the topic. – too much php Nov 14 '08 at 02:12
  • It was in the comments at the bottom of the article. I pulled out the relevant part. – Bill the Lizard Nov 14 '08 at 02:18
  • 7
    I believe the reason it takes a while on large tables is because MySQL has to read the through the entire table, not because it is building a giant index. I could be wrong. – too much php Nov 14 '08 at 02:38
  • 3
    @toomuchphp Yes actually "takes a while on large tables ...even if the column is ... all nulls" might as well be interpreted as "handling nulls is fast [because they are not indexed] but if table is huge .." – KajMagnus Jul 16 '12 at 05:51
3

Allowing a column to be null will add a byte to the storage requirements of the column. This will lead to an increased index size which is probably not good. That said if a lot of your queries are changed to use "IS NULL" or "NOT NULL" they might be overall faster than doing value comparisons.

My gut would tell me not null, but there's one answer: test!

J.D. Fitz.Gerald
  • 2,977
  • 2
  • 19
  • 17
  • 1
    Question was whether the index would increase in size. Answer was that it would increase the index size in the second sentence. – J.D. Fitz.Gerald Sep 07 '11 at 16:04
  • 3
    The *title* asks whether MySQL indexes null columns (it does). The *description* seems to ask a somewhat different question, but is really just an elucidation of why the (title) question was asked, in the first place. Moreover, since people largely choose whether or not to read a question based on its title, I'd say the title form overrides the description form, in most cases. – user359996 Sep 09 '11 at 01:14
  • 2
    Also, allowing a null column adds a byte to the *row*, not the column, unless there are already (a multiple of) 8 nullable columns, since the null is bitmapped. Indeed, this can very well *save* space, as null values only need to be stored in the bitmap. – user359996 Sep 09 '11 at 01:17
  • 2
    In this case, an INT column that is NULL 90% of the time takes 1 or less bytes 90% of the time, and between 4 and 5 bytes 10% of the time. This is, in the mean, significantly less than 4 bytes, all of the time, which is what the cost would be without allowing NULL. – user359996 Sep 09 '11 at 01:21
1

No, it will continue to include them, but don't make too many assumptions about what the consequences are in either case. A lot depends on the range of other values (google for "cardinality").

MSSQL has a new index type called a "filtered index" for this type of situation (i.e. includes records in the index based on a filter). dBASE-type systems used to have a similar capability, and it was pretty handy.

dkretz
  • 37,399
  • 13
  • 80
  • 138
1

Each index has a cardinality means how many distinct values are indexed. AFAIK it's not a reasonable idea to say indexes repeat the same value for many rows but the index will only addresses a repeated value to the clustered index of many rows (rows having null value for this field) and keeping the reference ID of the clustered index means : each row with a NULL value indexed field wastes a size as large as the PK (for this reason experts recommend to have a reasonable PK size if you have composite PK).

AbbasAli Hashemian
  • 168
  • 1
  • 7