MySQL Forums
Forum List  »  InnoDB

Re: Minimize locking on UPDATE with multiple WHERE columns
Posted by: Rick James
Date: March 28, 2013 12:04AM

> Are there times where innodb just uses the wrong index?

Yes. But I don't think you have provided the "right" index yet.

> Can I force it to use the right one by adding "use index" to the update query?

Yes, but let's not go there just yet.

> 2. I cannot understand how the transactions are taking less time after I added row2_row1_index and deleted row1_index although it was holding more locks?

Less time may imply that more stuff was cached in RAM. Or did you try each timing twice?

> Does it use different indexes for locking and for executing the query itself?

No.

> the transaction is holding ~300k (!) locks on row1_index (I would assume it should hold locks on row2_index according to the order in the WHERE clause).

MySQL will 'never' use two indexes in a single SELECT.

> 399K locks

Because it needed to lock the entire table.

> WHERE row2 > <some value> AND row1 IS NULL

For that you need this order:
INDEX(row1, row2)
To understand why, study
http://mysql.rjweb.org/doc.php/index1

Options: ReplyQuote




Sorry, you can't reply to this topic. It has been closed.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.