0

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.

halfer
  • 19,824
  • 17
  • 99
  • 186
Gargee
  • 11
  • 3
  • Possible duplicate of [Does mysql index null values?](http://stackoverflow.com/questions/289001/does-mysql-index-null-values) – MikeJRamsey56 Aug 02 '16 at 00:19

1 Answers1

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

Rick James
  • 135,179
  • 13
  • 127
  • 222
  • 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? – Gargee Aug 01 '16 at 23:05
  • 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 Aug 01 '16 at 23:24
  • Minutes / hours. Depends on lots of things -- size of table, setting of `innodb_buffer_pool_size`, spinning drives vs SSDs, etc – Rick James Aug 01 '16 at 23:25
  • Algo used: `CREATE INDEX name-index on table-name(column-name) algorithm=inplace` – Gargee Aug 01 '16 at 23:34