index; insertion of data?

[dodge20]

I was wondering if it was better to create indexes after insert data into a new table, or is it better to create the indexes then insert the data. I thought I hear it was the first, but if anyone knows for sure I would appreciate any info.

Dodge20

 

[Dimandja]

I don't know which database your are working with, or what your application requirements are.

But, normally, you would thoroughly design your database before using it. Indexes are integral parts of your database and are designed with the rest of the database.

Dimandja

 

[dodge20]

I know that, I already have the table designs planned out, I just wanted to know if it was better to add the indexes to the tables before or after I insert the data.

Dodge20

 

[psemianonymous]

I was reading a humongous thread about MySQL a while ago and they mentioned that setting a field to index AFTER a bulk insert was substantially faster, like 30 mins vs. many hours. So yes, there is some credence to your idea. If you're not worried how long the insert will take, then just go the simple route and have the index in place, even if it takes 5X as long.

 

[ArkM]

Insert data then make index is more, more faster than vice versa in all cases. Build B-tree (common index form with some variations) from scratch in indexing DBMS module is a very simple work. Add entry by entry (and rebuild index B-tree) in usual mode with implicit locking and other transaction processing on every step - nughtmare. Use 1st method when it's possible.

 

[CCLINT]

Yes, this is what I have always found...and heard.
With bulk insertions, the index gets rebuilt after each INDIVIDUAL insertion.

 

[dodge20]

Thank you,

That is what I wanted to know.

Dodge20