CODEWITHSUNDEEP
mysqldatabase-designdatabase-indexesdatabase-optimization
Kohjah Breese
Kohjah Breese

Reputation: 4136

MySQL: Performing Updates on a Large Table with Indices

Evening,

I have a large but simple table with nearly 30 million rows. The table has an index for most columns. It's taking a long time to perform any updates on it and I was wondering wheter it would be a good idea to delete the indices and re-built them once I have finished with my changes to the table?

The reason I ask is because I have read some posts on here where people haven't been able to re-build an index on a large table. However, I don't see why that should be as I can import this table from a 2.2GB text file in 20 minutes.

The table is comprised of a few small ints and varchars.

The update queries I ran were like:

UPDATE census SET rCo = 11470 WHERE rCo = 'Zet';

They weren't too slow, but I am currently changing one of the fields from varchar(4) to varchar(8) and it's taking a very long time.

I ran the update query from the command line (Linux).

Upvotes: 2

Views: 110

Answers (1)

Sebas
Sebas

Reputation: 21532

I think you should keep the index since you are using it in the where clause.

On the other hand, I'm pretty sure you should rather build up a brand new table:

CREATE TABLE census_2 (...) 
SELECT ..., IF(rCo = 'Zet', 11470, rCo) FROM census

and then drop the old one, + recreate the indexes on the new one AFTERWARDS.

Upvotes: 2

Related Questions