MySQL Forums
Forum List  »  Performance

Re: drop and recreate index in mysql DB
Posted by: Rick James
Date: November 29, 2015 11:38AM

Short answer:

CREATE table with all the indexes and don't worry about them.

Long answer:

* If you are loading one row at a time (that is a million INSERTs) into an existing table, have the indexes in place all the time.

* If you are _initially_ loading a table, and nothing else is going on other than loading the table, it _may_ be faster to have the PRIMARY KEY and FOREIGN KEYs in place, but add the secondary keys later.

* If you turn off / DROP an index, do some loading, then turn on / CREATE the index(es), that is bad if you are also accessing the table at the same time.

* If practical, use LOAD DATA with a million-row file. This will perform the load in the fastest(?) possible way.

Unrelated comments:

* Why is there a `prd01` and `prd01_stg`? They have the same schema?

* `prd01` -- does this mean that there is a `prd02`, `prd03`, etc? Yuck!

* Why are almost all the columns NULL? That seems unrealistic.

* I see 3 sets of code+type+name fields -- "arrays" usually should be be implemented as multiple columns; instead have another table. Ditto for ca_1/2/3.

Options: ReplyQuote

Written By
November 25, 2015 05:48AM
November 26, 2015 02:31PM
November 27, 2015 12:33AM
Re: drop and recreate index in mysql DB
November 29, 2015 11:38AM
November 30, 2015 01:34AM

Sorry, you can't reply to this topic. It has been closed.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.