What are the risks of RENAME TABLE inside a replicated mysql structure?

I have a cron script that creates an updated version of a mysql table that is 开发者_运维百科optimized for site searching purposes.

The table that is used in the site search is called tblSearch

the cronjob creates a new table wrkSearch and when it has finished to populate the table ends dropping tblSearch and renaming wrkSearch (both tables have MySam Engine)

DROP TABLE IF EXISTS `tblSearch
RENAME TABLE `wrkSearch` TO tblSearch

This is working fine, but I'd like to know if is a good approach to the problem.

I would like to know if your considerations change based on table size... for example: "the solution could be dangerous for very big tables cause..."

I know that every query that is changing something inside a mysql table is writing things on the filesystem... but are there some difference between doing a RENAME instead of a UPDATE/INSERT? I'm trying to understand if the RENAME command is more aggresive against the filesystem.

Another element on the table is the fact that te database is replicated with a MASTER-SLAVE structure... so I'd also like to know if this could eventually incrase the risks behind a RENAME query

Another aspect that is important to me is the amount of system resources that are used... could the RENAME operation be more greedy than UPDATE/INSERT?

---

You should change the order; first rename, than drop:

RENAME TABLE tlbSearch TO tblSearchDropMe, wrkSearch TO tlbSearch;
DROP TABLE tblSearchDropMe;

Since RENAME is atomic there is no way some other process will ever fail to access tblSearch; when first dropping, then renaming this might happen.

Apart from that I don't see any replication related problems with this.

---

The rename itself is super fast. If you are deleting everything from the table and creating it fresh then this method is fine. (Although please check that it does actually replicate properly.)

However regarding the replication it means the entire data of the table needs to be transmitted.

It's better, if you can, to update the data in the table - that way only some of the data changes, not everything.

I should point out that this type of manipulation is frowned upon - with good reason! But like all rules, sometimes you have to break them.