Working with HUGE tables

[nick-harper]

Tweet

Hey,

I am working on a largeish project which will involve a lot of automated updating of a database. (using myISAM)

This is the scenario, I have about 5 million records in a table and each link in to a categoryid.

Now, every night I need to automatically update the whole of the database which is effectively emptying it and repopulating it so that it is 100% up to date.

I have been doing it category by category (DELETE FROM WHERE categoryid="") and then adding everything into that category again.

The reason I am deleting them all and then readding is because I could do an update BUT it would then keep items that are no longer needed.

This is really slowing the application down though, as it is hammering the table for the live site.

I have thought that I might add a secondary table, fill this up, rename the current table to _backup and then rename the new table to the live table

I am stuck in a bit of a rut on the best way to update every night without it majorly slowing the site down.

Any ideas would be appreciated

[jonsp]

The way I do in the same scenario as you it is run 2 tables
LiveTable
TempTable
Every night run your updates into TempTable then when done push them into LiveTable

Unless the problem is that the script itself is slowing your live site?

[confuscius]

I have thought that I might add a secondary table, fill this up, rename the current table to _backup and then rename the new table to the live table.

This is the method that I adopt for maintaining large database price tapestry sites. If you are building from scratch, then delete all indexes, add your data and then add the indexes in at the end for a faster process on your secondary file.

[nick-harper]

The way I do in the same scenario as you it is run 2 tables
LiveTable
TempTable
Every night run your updates into TempTable then when done push them into LiveTable

Unless the problem is that the script itself is slowing your live site?

When you say push them into the live table, do you just rename the table or do a truncate / copy?

[jonsp]

delete from LiveTable
then
insert LiveTable select * from TempTable

[nick-harper]

delete from LiveTable
then
insert LiveTable select * from TempTable

How long does that usually take?

This will have around 5 million or so records so I didn't know if it would mean the site is empty for a while?

[confuscius]

Just rename you existing table to another name and then rename your secondary table to the existing table. Next time then delete the 'another name' table, rename existing to another and rename secondary to existing. Repeat.

You can always get back your original data this way. If you want extra backups then extend the rename chain to 'another another name'.

[nick-harper]

Thanks, I will give the empty / refill from temp table.

I just didn't know how long it would take for a rename of tables with 5 million or so records leaving the site empty / down. If I rename then the site would be down then, if I empty / refill it just means less products.