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?
+1
A:
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).
Bill the Lizard
2008-11-14 01:52:30
How did you come to that conclusion? I don't see any mention of the topic.
too much php
2008-11-14 02:12:16
It was in the comments at the bottom of the article. I pulled out the relevant part.
Bill the Lizard
2008-11-14 02:18:15
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
2008-11-14 02:38:19
+1
A:
Yes, it includles them, but don't make to0 many assumptions about what the consequences are. It probably won't improve the effectiveness of the index for queries, and may degrade it.
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.
le dorfier
2008-11-14 02:22:10
+2
A:
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
2008-11-14 10:45:04