database indexes - Does MySQL (InnoDB) index null values in a composite index? - Stack Overflow

admin管理员组

文章数量:1420907

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?

• mysql
• database-indexes

Share Improve this question Follow edited Jan 17 at 20:20 Barmar 784k5757 gold badges548548 silver badges659659 bronze badges asked Jan 17 at 20:14 giles3giles3 48722 silver badges99 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

Add a comment  | 

1 Answer 1

Sorted by: Reset to default 1

If 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.

本文标签： database indexesDoes MySQL (InnoDB) index null values in a composite indexStack Overflow