Slow query for update/delete sql statements

Question

In mysql there are lot of slow queries, only related to update and delete statements. The tables have 2 index columns and not heavily indexed tables. Each table having 30K records on average.

Please give your suggestions on how to overcome the slow queries related to update and delete queries. These kind of queries:

DELETE FROM <table2> 
 WHERE ID IN (SELECT ID 
                FROM <table1> WHERE ID2=100); 

...or:

UPDATE <table1> 
   SET <colmunname>=0 
 WHERE id=1001; 

Answer 1

The fact that your problem only exists on updates and deletes tells me that you are probably indexing too much.

Indexes will vastly reduce the time that certain queries take, but will require extra work when inserting, updating, and deleting entries from your tables. Try eliminating indexes on columns that are often getting updated, especially if they don't often appear in a where clause in your SQL queries.

Answer 2

First and foremost: Use stored procedures.

Next: If your db has optimizing capabilities -> use them.

Finally: Consider using no-relational dbs like CouchDB or Cassandra instead ;-)

Answer 3

Being that the tables are indexed, my first suggestion is to update the statistics for the tables using ANALYZE TABLE:

ANALYZE TABLE table1, table2

But beware:

During the analysis, the table is locked with a read lock for MyISAM, BDB, and InnoDB.

Answer 4

Managing indexes and foreign key relationships both incur overhead during update and delete operations. I would restore a copy of your prod db on a dev server, drop all foreign key constraints and all expect your primary key indexes and see the performance difference. Then you can add back your indexes until you have a better performance balance for your app.