Re: drop and recreate index in mysql DB
Posted by:
Ra Nala
Date: November 30, 2015 01:34AM
Rick James Wrote:
-------------------------------------------------------
> 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?
>
Ra nala :- Tables prd01` and `prd01_stg are in same/database. prd01_stg mean, it will be useful for incremental records, that is for cleansing and loading data purpose. and prd01 is my target datamart table which will maintain all history data.
----------------------------------------------------------
> * `prd01` -- does this mean that there is a
> `prd02`, `prd03`, etc? Yuck!
>
Ra nala :- exactly whatever you have guessed is correct, i have `prd02`, `prd03,prd04 these are for my multilingual data. and these are exactly useful in cogno framework manager to retrive multi language data based on locale logic.
----------------------------------------------------------------
> * Why are almost all the columns NULL? That seems
> unrealistic.
>
Ra nala :- No i am not having all columns having NULL data, my intention is if i
have null then is it better to replace all with NULL, as you mentioned in your thread.
--------------------------------------------------------------
> * 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.
Ra nala :- yes we can maitainf code,type,name columns as per my business logic example:
i have NOKIA mobile then it is having itecm code, what type code of phone it is, what type of name it is.
I hope, terminology will typical to you,but that is per my business implementation