Updating two tables at once
The CASE method seems to be efficient only for few updates (so you can make a cron job to run updates more frequently), but overall you can see clearly that you can still use transactions and get better results.I hope the method described in this post helped you or at least gave you more ideas on how to use it for any of your projects, and if you didn’t know about transactions, maybe you should give it more attention (I sure will from now on).
If you use Inno DB switch to My ISAM and them use transactions.In this article, we are going to look at four scenarios for Oracle cross table update. Category_ID) where exists ( select * from Categories b where b. These columns uniquely identify a record in a table.Suppose we have two tables Categories and Categories_Test. The common column in the two tables is CATEGORY_ID. Please note that query below is used for illustration purpose because Category_ID alone is primary key.So we can make a script to parse our logfile like this: Simple and efficient. Download link: parse_Just as Jesper pointed out using transactions instead of CASE can make a huge difference.Here are the results of some tests made on a 316,878 records database using both My ISAM and Inno DB storage engine.
Search for updating two tables at once:
I had a situation like this and using the method above got an order of magnitude performance improvement over the non-SQL way with only ~2000 records.