I was indexing a huge table today containing 2 billion records. I thought MySQL would eat away my 2TB drive... The disk consumption kept increasing to 400GB and then 500GB and then finally drops to 180GB and MySQL says successfully added the index. Why the space increase and what happened in the end? Can someone please give me some pointers?
+4
A:
Incidentally yesterday I answered a question on how to make index creation faster in MySQL, and the following came out from my research:
The
CREATE INDEXandDROP INDEXcommands work by creating a new, empty table defined with the requested set of indexes. It then copies the existing rows to the new table one-by-one, updating the indexes as it goes. Inserting entries into the indexes in this fashion, where the key values are not sorted, requires random access to the index nodes, and is far from optimal. After all rows from the original table are copied, the old table is dropped and the copy is renamed with the name of the original table.Source: Overview of Fast Index Creation
Daniel Vassallo
2010-10-03 08:01:41
There is a sql command "SHOW PROCESSLIST;" which you can use to monitor what mysql is doing and which will confirm this.
nhnb
2010-10-03 08:23:13
@Daniel: Very interesting... Thank you for the insight. One of my problems is that I wasn't aware that InnoDB supports partitioning so I resorted to using MyISAM. Not sure if converting the engine to InnoDB at this point is logical or not.
Legend
2010-10-03 17:58:44