Re: Shudden Slow down of MySQL Queries

[jayaram pagoti]

James, Really great ! about these suggestions.

>> SELECT @cutoff := id FROM tbl WHERE ... ORDER BY id LIMIT 1000,1;
>> DELETE FROM tbl WHERE ... AND id < @cutoff ORDER BY id;

Actually, as you guess our table design is not support to delete the rows based on id( ex data : 1-room1-2014-10-01 , 2-room1-2014-10-02, 3-room2-2014-10-01 ...)

total number of rows in this table : 42577651. daily we are deleting 64000 rows ( which are relate to a day ).

Hence, based on your suggestions and all. We are planning to delete 1000 rows at a time ( instead of deleting all rows in a single transaction, we are planning commit the data changes for every 1000 rows deletion. )

Please suggest !