MySQL: How does nullable columns function in joint index?

Question

I have two columns in a MySQL table, one is startDay varchar(10)，like "2020-03-23", the other is orderId archer(20), both of them are nullable

If I create a joint index on (orderId, startDay)

  select * from table where orderId is null and startDay < '2020-03-23'

  select * from table where orderId is not null and startDay < '2020-03-23'

for the above 2 sql statements, I have two questions: 1) does the first statement use both columns in the joint index? since there are tens of millions of rows, if the statement doesn't use both, but only use part of the index (namely the first one), then the performance may be low?

2) the second one can't use the joint index? why?

@nbk seems explain doesn't tell us how many columns in a joint index are used? — lily
– lily, Commented Mar 17, 2020 at 17:51
without viewing the explain result, it is quite hard to tell. what your database does, it is besides a create table a possibility to see what is used see last column — nbk
– nbk, Commented Mar 17, 2020 at 18:00
@nbk 1 SIMPLE bill NULL ref idx_repaid_time,idx_due_date_status,i_d_r i_d_r 7 const 13 45.00 Using index condition — lily
– lily, Commented Mar 17, 2020 at 18:23

Uueerdo · Accepted Answer · 2020-03-17 17:58:51Z

1

The first can make full use of the index because it only has one range-based condition, and that is for the latter field in the index.

Basically, it goes something like this:

orderId IS NULL looks for a specific node in the index identifying all IS NULL entries,
that node's child nodes are effectively sorted by startDay (the second part of the index)
the range of startDay nodes where startDay < '2020-03-23' are easily identified.

With the second query, orderId IS NOT NULL basically identifies every first tier node other than the one that identifies the IS NULL orderId values.... and the MySQL won't go further with the indexing. (It stops at the first ranged condition).

Also, it's not so much a matter of having a nullable column so much as the first query has the equivalent of an = condition, and the second has a <> condition.

edited Mar 17, 2020 at 17:58

answered Mar 17, 2020 at 17:49

Uueerdo

16k1 gold badge19 silver badges21 bronze badges

Sign up to request clarification or add additional context in comments.

4 Comments

lily Over a year ago

seems explain doesn't tell us how many columns in a joint index are used?

Uueerdo Over a year ago

I do not believe so... just which index (if any) is used.

The Impaler Over a year ago

Yep, typically indexes won't use inequality-like operators. as in the second case.

Rick James Over a year ago

@Uueerdo - You can usually deduce from Key_len how many columns are used (INT NOT NULL: 4, INT NULL: 5, etc). However Key_len does not include the columns used for ORDER BY. Need to see EXPLAIN FORMAT=JSON for that.

Collectives™ on Stack Overflow

MySQL: How does nullable columns function in joint index?

1 Answer 1

4 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

4 Comments

Your Answer

Sign up or log in

Post as a guest

Related