Re: Delete query very slow and locking other tables

[Rick James]

Some approaches:

* Add "LIMIT 20" to the DELETE; loop until no more are deleted.

* Partition your table into 25 hourly tables. Repartition, create new table, and drop the old table once an hour. (This assumes MySQL 5.1 with Partitions.)

* If session_ids are ordered chronologically, figure out what range of session_ids match the period to delete, then delete by the PRIMARY KEY (session_id).

* (If practical) Batch your INSERTs -- that runs them much faster.

* ANALYZE TABLE tbl_sessio ns; -- shouldn't help, but might convince the DELETE to work smarter.

* Run EXPLAIN SELECT * tbl_sessions WHERE expire_time < ? \G -- This might give clues of why the DELETE is being naughty.