CODEWITHSUNDEEP
mysql
user42232
user42232

Reputation: 3

MYSQL: Update replace on 2million records

Ok so I have over 2 million phone numbers in one table and I need to remove all the spaces from the phone field.

I have INDEX the phone field and optimised the table but still when i run the following query it is so slow and takes forever - in fact I am still waiting and 30minutes have past

UPDATE ACTNSW SET phone = REPLACE(phone, ' ', '');

I need to know if there is away to speed up this process so that it does not take so long.

Database scheeme it is using is InnoDB Server version: 5.5.31-1 (Debian)

Upvotes: 0

Views: 82

Answers (2)

ianaré
ianaré

Reputation: 3298

If you have the possibility of doing the update offline, you could use mysqldump to output the contents of the table. Next perform your replace operation on the file with sed/awk. Then drop your table on the server and restore it with the dumpfile.

Upvotes: 1

Hiren Dhaduk
Hiren Dhaduk

Reputation: 2780

this is basic sql query . you can't do much about it but you can do one thing don't run update
query for whole table . make multiple update queries something like ..

UPDATE ACTNSW SET phone = REPLACE(phone, ' ', '') where id < .2 milian
UPDATE ACTNSW SET phone = REPLACE(phone, ' ', '') where id < .4 milian and id > .2 milian
UPDATE ACTNSW SET phone = REPLACE(phone, ' ', '') where id > .5 milian and id > .4 milian
....

I think it will help you .

Upvotes: 2

Related Questions