Slow query for update/delete sql statements

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; 

Answers


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.


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.


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.


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 ;-)


Need Your Help

Renren, Weibo, and Baidu Like buttons using only HTML (No Javascript)

html url social-networking weibo sinaweibo

I am working on a website that will marketed in China, and I would like to know if there is a method to create "share" buttons for Renren (Similar to Facebook), Weibo (Similar to Twitter) and Baidu...

Inject ConversationScoped beans within an asynchronous method

jsf asynchronous ejb conversation-scope

I need to call a method annotated with @Asynchronous in EJB from a ConversationScoped bean. Inside this method I create instances of some classes using @Inject to inject ConversationScoped beans.