Suppose I have a table with 1 million rows and lots of columns. X and Y are two of the varchar columns. For 0.1% of the rows both X and Y have a value. For the other 99.9% of the rows both X and Y are null. Suppose I create a composite BTree index on (X,Y).
Is MySQL going to index all those nulls?
I will get great performance when I query on X alone or X and Y together. But will I pay a small penalty every time I insert or update a new record where X and Y are both null?
Suppose I have a table with 1 million rows and lots of columns. X and Y are two of the varchar columns. For 0.1% of the rows both X and Y have a value. For the other 99.9% of the rows both X and Y are null. Suppose I create a composite BTree index on (X,Y).
Is MySQL going to index all those nulls?
I will get great performance when I query on X alone or X and Y together. But will I pay a small penalty every time I insert or update a new record where X and Y are both null?
Share Improve this question edited Jan 17 at 20:20 Barmar 784k57 gold badges548 silver badges659 bronze badges asked Jan 17 at 20:14 giles3giles3 4872 silver badges9 bronze badges 3- I can't think of why it wouldn't. The index needs to contain all the values. – Barmar Commented Jan 17 at 20:17
- I don't see how this situation is any different from any other table where 99.9% of the rows have the same values in indexed columns. The index won't be very effective in this case. – Barmar Commented Jan 17 at 20:19
- Give it a try and let us know. – Rick James Commented Jan 23 at 3:46
1 Answer
Reset to default 1If you create an index on table(col1, col2), and you do WHERE col1 = 'something' AND col2 = 'something' you'll exploit the index; it will be very selective. Same with WHERE col1 = 'something'.
But WHERE col1 IS NOT NULL you'd have to test. WHERE col1 IS NULL would scan the table because the index is not at all selective in that situation.
Updating an index on INSERT or UPDATE does exact a small overhead. This can't be a UNIQUE index, so the workload is a little less. But it's not worth worrying about in most cases, unless your INSERT / UPDATE workload is far heavier than your SELECT workload. If you can make the columns have COLLATE latin1_bin you'll get the lightest weight possible indexes. But no emoji, Chinese characters, or case insensitivity.