I'm performance tuning a very data-heavy mysql application. It has a purge system that often seems to cause lock timeout errors for users. Based on my research I have a few adjustments to the query to make to ensure we're locking as few records as possible... but
Is there any way to get a count of how many records mysql will lock for a given query? I want to be able to see evidence that my adjustments are requiring fewer locks.
In this specific case, I have some subqueries for foreign key data since I can't use LIMIT and JOINs. I'm wondering what impact, if any, the subquery has on the delete query locking.
I'm also considering the "primary key chunking" described here.