i've an problem with my current database with mysql.

i've over 100 connection waiting on the choose record. after i execute:

show processlist;

the choose totally a large query and also the other medication is smaler queries and card inserts, updates.

i've one database with 100 tables and also the choose is applying 5 joins.

it is possible to method to temporary steer clear of the process and allow the other processes run, once all of the processes are completed, then your choose can continue running?

i'll recommend to allow the query do what it must do, should you stop any or kill processses or queries you may have data integrity errors be responsible for major errors.

BookOfZeus and tfb785 are right, to begin with you most likely have indexes errors. the explain will explain what is the issue and things to search for. for instance for those who have 5 joins and also you get row counts like, 100,000 and 100 and 1 and 1 and 1 you'll multiple 100,000 * 100 which may be super slow.

read carefully exactly what the explain informs you and also optimize your query according to it.

innodb could be a wise decision should you when the tables are utilized very frequently because its row securing insted of table securing for myisam.

I'd say try to optimize your query, you may wont have to change your table engine to repair the problem. if you've still got issues then you definitely might consider moving to innodb.

It is possible to method to temporary steer clear of the process and allow the other processes run, once all of the processes are completed, then your choose can continue running?

In my opinion you're using MyISAM like a storage engine.

In MyISAM, the read queries block the concurrent write queries. This makes certain that the creates will not alter the data in process and you do not get 1 / 2 of your recordset up-to-date and the other half not up-to-date.

There's not a way to prevent the SELECT query how you request: the creates might make the recordset came back inconsisent. Although it might be not the particular situation inside your setup and theoretically creates may not modify the reads (say, different records are read and written), MyISAM is unaware of might it always blocks whole tables, just just in case.

If you want creates to not block reads, change to InnoDB (though you will find times when creates will block reads too).

You cannot steer clear of the running query and also to give time for you to another queries. But you will find 3 ways to resolve your condition:

First way: Alter the isolation level http://dev.mysql.com/doc/refman/5.6/en/mysql-acid.html These may also mean you need to improve your storage engine eg. to InnoDB.

Second way: Attempt to speedup your lengthy running choose with indices. This can be a so known as time memory downside. You speedup your query with increased memory for holding the indices trees.

Third way: Speedup your lengthy running choose with ordering the query (joins and chooses). Evaluate the price of all query parts.