Link to home
Start Free TrialLog in
Avatar of niidmore
niidmoreFlag for United Kingdom of Great Britain and Northern Ireland

asked on

drop/add unique key on the fly

Hello Guys,
I have a situation where I would need to add/drop unique key from the table. Please advice/help with the best approach.
Regards
Sam
Avatar of hielo
hielo
Flag of Wallis and Futuna image

To add a UNIQUE key on the fly you are better off using an autoid/autonumber field. To drop the key, you will need to know the key and just do a DELETE based on that key.
Here's the instructions:

http://dev.mysql.com/doc/refman/5.1/en/create-index.html
http://dev.mysql.com/doc/refman/5.1/en/drop-index.html

The syntax is actually an alias for an ALTER TABLE command, but the results are the same.  

A better question is do you really need to add/drop a unique index?  I can understand adding one.  I can even understand dropping one under certain conditions.  Adding and dropping repeatedly I'm not so sure about.  Can you explain your scenario a little?
Avatar of niidmore

ASKER

Hello,
i have a table of million records with id field set to primary key,
there is another field which is set to unique index.
I am using load data infile to replace the existing million records with the new csv file.
It seems REPLACE is actually not replacing existing records but adding duplicates to tit.

type id   title       price
ac    1    abc      10
cx    2    xyz       20
td     3    mnp       30

to the above sample table data, if i add the following

type  id   title       price
kk     1    kkk      10
oo     2    zzz       20
pp     3    xxx       30

the resulted recordset is looking something like this

type id   title       price
ac    1    abc      10
kk     1    kkk      10
cx    2    xyz       20
oo     2    zzz       20
td     3    mnp       30
pp     3    xxx       30

although the id is set as a primary key, it is showing the duplicate data, may be because i have indexed id,title on the table. is the REPLACE behaving so because of the index?

please advice.
regards
sam


//REPLACE:
ql= "LOAD DATA LOCAL INFILE 'e:/fes20080227.csv'" &_
     " REPLACE INTO TABLE temp_fes_web_data " &_
     " FIELDS TERMINATED BY ',' " &_
     " LINES TERMINATED BY '\n' " &_
     " (id,price,title,discount,rrp);"
 
cnObj.Execute(sql)
well i dont really think i understand the unique index concept, i thought every field which is used in the search criteria should be indexed to fetch the records quickly, i have seen the huge difference in processing 1 million records with and without an extra index on the title field, if i have it indexed then the processing of entire recordset took about 60 mins whereas it  took good number of hours to process without the index field.

please advice
ASKER CERTIFIED SOLUTION
Avatar of Steve Bink
Steve Bink
Flag of United States of America image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
many thanks for your neat explanation, i definitely dont need multiple fields to be indexed as well as unique,  i have to index the title filed separately.
regards
sam