A Question on MyISAM table locking on joins.

[John Snow]

Recently we had a crash that inadvertantly switched our DB over from Innodb to MyISAM. I just want to confirm a behavior I was seeing in the slow log.

There were certain queries coming through that did multiple joins across many tables where the inner most table was a huge regex search on a varchar field on a table with nearly a billion records in it that was forcing a full table scan. This query if ran multiple times would cause locks to occur.

My question is that since this is an abysmally slow query, does that query also lock every table in the join that is a part of the query, or just the table that it's currently searching on?

I was noticing huge performance issues caused by the accidental switch over, and I was wondering if possibly cascading table locks were occuring from the query mentioned above that was locking out othe tables in the system that other users might have been using?

Thanks for the help!



Edited 1 time(s). Last edit at 08/01/2012 09:17AM by John Snow.