Re: MySQL Performance Improvement Needed Urgently

[James Day]

Which database engine is in use? MyISAM or InnoDB are most likely - InnoDB if you want transaction and ACID support. What else is happening while the slow insert is happening? SHOW PROCESSLIST and SHOW FULL PROCESSLIST will tell you this.

If it's InnoDB, beware of SELECT ...FOR UPDATE. I've seen a case where someone tried locking a whole table for update that way instead of just using LOCK TABLE. Better to lock the table instead of each record one at a time.

What is the write doing?

If it's an update, how is the write identifying the record to change? I've seen someone do a write which did a full table scan to find the record to change, instead of using an index. Adding a suitable index would help this.

Only some tables have indexes? I assume all have primary key/index, but no others? Sounds as though you're going to need to add some more indexes as part of tuning.

A routine question, just in case you're or they are new to MySQL. Is there a my.cnf file? Very common for people new to MySQL not to have one and it has a very great effect on query speed.

If you haven't done it yet, and the server isn't in production use, you might see if OPTIMIZE TABLE tablename for every table helps. Sometimes people end up with very inaccurate query optimizer statistics and analyze or optimize can help a lot.