S

Sekira2013-02-07 16:03:53

MySQL

Sekira, 2013-02-07 16:03:53

Is it possible to delete records from MyISAM without locking the table for reading

There is a MyISAM table of the form id, text (about 1 kb each record), an index only by id, about 200,000 rows, when deleting 1 or 100 records with a query like delete ... where `id` in (1, 50, 60) , the table is not available to read about 10 seconds.

Whether there are variants without change of type of the table to delete records without blocking reading? Thank you!

Reply

Answer the question

In order to leave comments, you need to log in

4 answer(s)

V

Vyacheslav Plisko, 2013-02-07
@AmdY

200k rows from a small table, the deletion should take place instantly, certainly not seconds. It looks like you have a problem in another place, see how not to lose the entire table, this happens with MyISAM.

I

Ilya Evseev, 2013-02-11
@IlyaEvseev

dev.mysql.com/doc/refman/5.0/en/delete.html - LOW_PRIORITY keys and especially QUICK keys.
dev.mysql.com/doc/refman/5.0/en/delete-speed.html - increase key_buffer_size.

A

afiskon, 2013-02-07
@afiskon

I'm no expert, but I don't think so. Any change to MyISAM tables causes the entire table to be locked. If you do this a lot, you might want to switch to InnoDB (or an alternative row-level locking engine).