MySQL Forums
Forum List  »  Performance

Re: Terrible performance of a specific query with lots of connections
Posted by: Rick James
Date: September 10, 2009 07:27PM

Trouble with open tables? Increase the limits -- esp. open_files_limit. If the OS is restricting you, then increase its per-process limit.

Split this into two DELETEs:
DELETE FROM document_references
  WHERE referenced_document_id = '118084217'
                OR document_id = '118084217'
Why? MySQL did not used to be able to use two indexes in a single query (which this would need). What version are you running? Also, you could perhaps get a good clue via
    FROM document_references
      WHERE referenced_document_id = '118084217'
                    OR document_id = '118084217' ;
In any case need something like these on document_references:
INDEX(referenced_document_id) and

To assist in analyzing slow SELECTs, please provide
* SHOW CREATE TABLE tbl\G -- engine, indexes
* SHOW TABLE STATUS LIKE 'tbl'\G -- sizes
* EXPLAIN SELECT ...\G -- clues of inefficiencies
* SHOW VARIABLES LIKE '%buffer%'; -- cache size
and surround them with [ code ] and [ / code ]

Please do the EXPLAIN on any SELECTs that may be running when the things are slow. What do you see running? SHOW FULL PROCESSLIST.

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.