MySQL Forums
Forum List  »  Performance

Re: Terrible performance of a specific query with lots of connections
Posted by: Rick James
Date: September 14, 2009 10:34AM

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.

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:

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.

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.