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? Link to comment https://forums.phpfreaks.com/topic/245264-is-there-a-quickermore-effient-way-to-do-this/ 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? Link to comment https://forums.phpfreaks.com/topic/245264-is-there-a-quickermore-effient-way-to-do-this/#findComment-1259689 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 Link to comment https://forums.phpfreaks.com/topic/245264-is-there-a-quickermore-effient-way-to-do-this/#findComment-1261126 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 ) Link to comment https://forums.phpfreaks.com/topic/245264-is-there-a-quickermore-effient-way-to-do-this/#findComment-1261129 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. Link to comment https://forums.phpfreaks.com/topic/245264-is-there-a-quickermore-effient-way-to-do-this/#findComment-1261141 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` Link to comment https://forums.phpfreaks.com/topic/245264-is-there-a-quickermore-effient-way-to-do-this/#findComment-1261575 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. Link to comment https://forums.phpfreaks.com/topic/245264-is-there-a-quickermore-effient-way-to-do-this/#findComment-1261577 Share on other sites More sharing options...
Recommended Posts
Archived
This topic is now archived and is closed to further replies.