Re: Terrible performance of a specific query with lots of connections

[Rick James]

Well, I go back to your statement "rewritten as a subselect to try and reduce the amount of open tables, and to reduce the thrashing" -- and suggest that you work on increasing the limit on open tables.

SHOW GLOBAL STATUS LIKE '%open%';
SHOW GLOBAL STATUS LIKE 'Uptime';
In particular, I am looking for Opened_files/Uptime and Opened_tables/Uptime. If these are << 1/sec, then opening is not your problem. If it is >> 1/sec, then look into increasing the limits.

I was pleasantly surprised to see that OR turn into UNION -- I did not realize that such an optimization got into 5.0.51a. It says that the DELETE may be quite efficient (in spite of showing up in the INNODB STATUS). I say "may" because not everything transfers from EXPLAIN properly to DELETE.

Somewhat unusual to have a NULLable field in a UNIQUE key:
`profile_id` bigint(20) unsigned default NULL,
UNIQUE KEY `idx_profile_document` (`profile_id`,`document_id`)

I wonder if the "freeze" would go away if you did the query in two steps:

CREATE TEMPORARY TABLE x
SELECT document_id FROM profile_documents WHERE profile_id = '23890';

and then do some kind of self join on x. (It may also help to index x.)
Caution: If you are inside a transaction, there may be ramifications. Consider even making x ENGINE=MEMORY. It would not be "transaction-safe" but might give you the 'right' answer anyway.