Most efficient way to update database in multi-user environment

[monkey64]

I have an online database with 4k records. Updating is done with a php update script which first drops the old table, creates a new one and inserts the data. It takes about 2 minutes and during this time, the site is unresponsive.

Is there a better way to achieve this. Perhaps creating a second table, and renaming it?

 

[jaxtell]

Perhaps, but there could be consequences. What happens if there is an update or insert to the old table while you're creating the new one? Can you really not take the site down for 2-5 minutes to perform an upgrade?

-----------------------------------------
I cannot be bought. Find leasing information at

http://www.joshaxtell.com/

 

[ingresman]

Can you not update the table in-situ ?

 

[vacunita]

Agreed, Is it really necessary to drop the entire table.

If you are really updating, why do you need to drop the table, just update the relevant records. should be quicker, and the table would remain online.


----------------------------------
Phil AKA Vacunita
----------------------------------
Ignorance is not necessarily Bliss, case in point:
Unknown has caused an Unknown Error on Unknown and must be shutdown to prevent damage to Unknown.

 

[monkey64]

The table contains stock items. Some items get taken off the website, others get price changes, new products get added daily. This table is read only, i.e there will never be an update or insert.

I've tried viewing the website when I run an update and it's SLOW. My 4000 records take a massive 2 minutes to execute. That seems a long time...

I've been experimenting with creating a temporary "temp_Stock" table, then deleting my old "Stock" table, then renaming "temp_Stock" to "Stock". This causes less disruption to the site as it is down for only 1-2 seconds.

 

[ingresman]

From a technical perspective it would be interesting to know why your update takes so long. but in the interests of just getting on with things I'd stick with your 1-2 second solution.