Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

I have noticed that even on huge tables with millions of records when I do a CREATE INDEX the index is created immediately (I get the console back in nsecs).
Why is this? Doesn't it create the B-Tree with the table data at that point? I can only assume not due to the immediate return of the CREATE INDEX.
But then how is the index created? On each access?

share|improve this question
    
By the way, innodb, despite the SQL interface saying BTREE, uses B+Trees. –  jynus Nov 18 '13 at 6:22
add comment

1 Answer

Secondary index creation on innodb has been much faster since MySQL 5.1 + InnoDB plugin or MySQL 5.5. In fact, we refer to this feature as "fast index creation". If we don't touch the primary key, it only creates an additional structure and it only has to read the current values, without modifying the table data. Assuming you have enough memory, if the data pages are in memory and the index also fits into memory- this will be a memory-only operation, so potentially very fast. But the index will be ready to be used as soon as the process ends. In the background (after you get control back to the console), it will write the new pages to disk whenever it can. It is also dependent on how much data fragmentation you have in that particular tablespace. None of this can lead to losing data due to the transaction log (which is on disk).

But you are completely right by saying that some changes on secondary indexes are done in the background: it is called Change buffer, but it is used when modifying data records.

I must say that in my experience, InnoDB index creation is not as fast as it could :-), specially on very busy servers. However, starting with 5.6, creating secondary indexes is completely online (writers to that table are not blocked), which is a very nice improvement indeed.

share|improve this answer
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.