I have a chat_log table which store data of sender and receiver. Structure is as
SENDER RECEIVER DATE
Current implementation: every month I create a MyIsam table say JAN to store all the chat done in January, FEB store all the chat done in February and so on.
say on MARCH 31 is have a merge table chat_log
which contains multiple chat table JAN,FEB,MAR
On 1st april, I need to change the definition as FEB MARCH AND APRIL.
merge table work best here. Problem is when ever a user deletes its entry, i need to remove that entry from the table, since the table are merge myisam, this is happening very slowly.
so what should i do:
I believe you should use partitions rather than the MERGE storage engine.
The appropriate partitioning type seems to be RANGE.