Pause and resume running mysql query

Question

I have got quite slow query (actually this is data loading from mysql.dump). Now I want to check how many rows are already loaded.

select count(*) from my_table;

As far as data is loading, my_table is locked, so I can't execute this sql before dump is loaded.

Cmd       ID   State               User   Host           DB      Time   Query                           
Query     191  update              root   localhost      rehub   00:09  INSERT INTO `my_table` VALUES ...
Query     189  Waiting for table   root   localhost      rehub   06:25  select count(*) from my_table

So is there a way to pause query 191, execute query 189 and then resume query 191?

Answer 1

No there is not a way to suspend a query.

If you're doing bulk load work you might want to use a bulk loader that commits every few thousand rows.

Also, using MyISAM instead of InnoDB will alleviate this problem. MyISAM is a good choice for bulk processing where you're not primarily concerned with transactional integrity.