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