Mysql Innodb indexing on a column with majority null values

Question

I tried to index a date time field in a table with 20 million rows. Majority(99%) of the rows for that column are null. After that, CPU utilization shot up to 100% because of this. I'm not able to find out the exact reason for the same.

Possible duplicate of Does mysql index null values?

MikeJRamsey56
– MikeJRamsey56

2016-08-02 00:19:18 +00:00
Commented Aug 2, 2016 at 0:19 — MikeJRamsey56
– MikeJRamsey56, Commented Aug 2, 2016 at 0:19

Rick James · Accepted Answer · 2016-08-01 22:55:45Z

0

As an optimization, InnoDB "delays" the updating of indexes. Probably what happened is that this delayed operation is happening, causing the CPU to spike.

For further info, search for "InnoDB change buffering".

answered Aug 1, 2016 at 22:55

Rick James

144k15 gold badges144 silver badges255 bronze badges

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

4 Comments

Gargee Over a year ago

I deleted the indexes once CPU spiked up. I need to be sure of the root cause before I create the indexes again. Any pointers for debugging? Any estimate for it's delayed index update to complete?

Rick James Over a year ago

20M rows -- They have to be scanned, a file created and sorted, then written back to disk, etc. Please show us the command you used and the SHOW CREATE TABLE.

Rick James Over a year ago

Minutes / hours. Depends on lots of things -- size of table, setting of innodb_buffer_pool_size, spinning drives vs SSDs, etc

Gargee Over a year ago

Algo used: CREATE INDEX name-index on table-name(column-name) algorithm=inplace

Collectives™ on Stack Overflow

Mysql Innodb indexing on a column with majority null values

1 Answer 1

4 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

4 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related