MySQL Forums
Forum List  »  InnoDB

Re: Innodb bogs down on complicated query(ies)
Posted by: Marko Mäkelä
Date: May 05, 2006 01:39AM

Without the UPDATE statements, it is hard to guess what is going on. Are the UPDATEs affecting a single row (WHERE condition on the primary key), or are the statements affecting several rows? If the latter, can a secondary index be utilized? Are there any foreign key constraints that specify ON UPDATE CASCADE? If yes, how many tables and rows would be affected by the UPDATE?

According to the SHOW INNODB STATUS output, the insert buffer (for making inserts to secondary indexes more sequential) is not being used. This would suggest that the UPDATEs are not modifying large numbers of rows. I can't see any obvious reason for the stall.

One last thing: you should consider making the primary keys shorter, i.e., by using auto-increment 32-bit (4-byte) integers as primary keys and by defining unique secondary indexes on the primary key columns. If I interpreted the EXPLAIN SELECT output correctly, you have primary keys as long as 32 bytes.

Best regards,

Marko Mäkelä
Innobase Oy/Oracle Corp.

Options: ReplyQuote

Written By
Re: Innodb bogs down on complicated query(ies)
May 05, 2006 01:39AM

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.