Kryptix Posted August 20, 2011 Share Posted August 20, 2011 DELETE FROM `chat_logs` WHERE `time` < UNIX_TIMESTAMP() - 604800; It takes over 20 seconds as it has like 2 million entries. Is there a quicker way it can be done? Quote Link to comment Share on other sites More sharing options...
gizmola Posted August 20, 2011 Share Posted August 20, 2011 Is there an index on the time column? What do you get when you do EXPLAIN EXTENDED SELECT FROM `chat_logs` WHERE `time` How many rows are being deleted? What engine is the chat_logs table using? Quote Link to comment Share on other sites More sharing options...
Kryptix Posted August 23, 2011 Author Share Posted August 23, 2011 EXPLAIN EXTENDED SELECT * FROM `RSCEmulation Logs`.`game_chat` WHERE `time` < UNIX_TIMESTAMP() - 604800 Returns: Storage engine: MyISAM Quote Link to comment Share on other sites More sharing options...
Kryptix Posted August 23, 2011 Author Share Posted August 23, 2011 Selecting is really quick (0.006 seconds) but deleting takes 22 seconds... 211689 row(s) deleted. ( Query took 22.6486 sec ) Quote Link to comment Share on other sites More sharing options...
fenway Posted August 24, 2011 Share Posted August 24, 2011 I don't see why it's not using any key. Quote Link to comment Share on other sites More sharing options...
gizmola Posted August 24, 2011 Share Posted August 24, 2011 That is a lot of rows to delete, so it may just be that 22 seconds for a large delete like that on the hardware you're using, with the resources and contention on the server, is going to take that long. It's probably relevant to the total number of rows as well. To fenway's question -- that is a good question, but i suspect that it might be an optimization decision. Can you provide a: SHOW CREATE TABLE `RSCEmulation Logs`.`game_chat` Quote Link to comment Share on other sites More sharing options...
fenway Posted August 24, 2011 Share Posted August 24, 2011 Maybe the ANALYZE TABLE stats are off. Or maybe that matches more the 30% of the table. Quote Link to comment Share on other sites More sharing options...
Recommended Posts
Join the conversation
You can post now and register later. If you have an account, sign in now to post with your account.