What is the best way to update a mysql table based on a where clause from two tables ?

Question

I want to do something as shown below, but when I do it seems to take forever to action and I can't figure out why :

self.cursor.execute('update '+DB_NAME+'.tableOne, '+DB_NAME+'.tableTwo,\
SET variable=CASE \
WHEN variable+1> 100 THEN 100 \
ELSE variable+1\
END \
WHERE tableOne.variableTwo>0 AND tableTwo.variableOne>0;')

The statement is a little different obviously but that's the principal of what I want to do.

I want to update a table with a set/case/else/where and have two different tables in the where clause... It does action the update, but it takes ten to fifteen seconds to perform instead of the milliseconds if I remove the second table.

Any help would be appreciated.

I've sorted it.

Never mind.

Answer 1

Ok two things that can be done to speed up the process. First Update one table at a time. There is no problem in joining 2 tables in the where clause however your code actually updates the variable column in both tables. That takes a lot of processing power for mySQL . Another issue that you have here is that you do not use indexing. Indexing is the back bone of any RDBMS. a fast and simple way of using indexing here would be to create a unique key value in both tables relate one to the other with the relationship. and use it in your where clause.