I’ve sent over 500k texts and the database/website are getting pretty slow. I want to prune old records from the database, as I send a daily report to Amazon S3 and no longer need the data. What is the best way to do this?
Hi Scott,
I had recently the same problem. I got better performance deleting records from tables such as outgoing, outgoing_queue, billing, queue_dst, dlr.
Additionally, I have created indexes on some of above table.
Edilson.
I’d be very interested to hear about your indexes.
Did you delete records based on < date?
Yes, Scott, I did.
My indexes are as follow:
mysql> show indexes from playsms_tblSMSOutgoing;
±-----------------------±-----------±----------±-------------±------------±----------±------------±---------±-------±-----±-----------±--------+
| Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment |
±-----------------------±-----------±----------±-------------±------------±----------±------------±---------±-------±-----±-----------±--------+
| playsms_tblSMSOutgoing | 0 | PRIMARY | 1 | id | A | 475519 | NULL | NULL | | BTREE | |
| playsms_tblSMSOutgoing | 0 | smslog_id | 1 | smslog_id | A | 475519 | NULL | NULL | YES | BTREE | |
| playsms_tblSMSOutgoing | 1 | uid | 1 | uid | A | 13 | NULL | NULL | | BTREE | |
| playsms_tblSMSOutgoing | 1 | idx_smsc | 1 | p_smsc | A | 13 | NULL | NULL | | BTREE | |
±-----------------------±-----------±----------±-------------±------------±----------±------------±---------±-------±-----±-----------±--------+
4 rows in set (0.01 sec)
mysql> show indexes from playsms_tblSMSOutgoing_queue;
±-----------------------------±-----------±-----------±-------------±------------±----------±------------±---------±-------±-----±-----------±--------+
| Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment |
±-----------------------------±-----------±-----------±-------------±------------±----------±------------±---------±-------±-----±-----------±--------+
| playsms_tblSMSOutgoing_queue | 0 | PRIMARY | 1 | id | A | 42851 | NULL | NULL | | BTREE | |
| playsms_tblSMSOutgoing_queue | 0 | queue_code | 1 | queue_code | A | 42851 | NULL | NULL | | BTREE | |
| playsms_tblSMSOutgoing_queue | 1 | idx_flag | 1 | flag | A | 2 | NULL | NULL | | BTREE | |
±-----------------------------±-----------±-----------±-------------±------------±----------±------------±---------±-------±-----±-----------±--------+
3 rows in set (0.01 sec)
mysql> show indexes from playsms_tblSMSOutgoing_queue_dst;
±---------------------------------±-----------±---------±-------------±------------±----------±------------±---------±-------±-----±-----------±--------+
| Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment |
±---------------------------------±-----------±---------±-------------±------------±----------±------------±---------±-------±-----±-----------±--------+
| playsms_tblSMSOutgoing_queue_dst | 0 | PRIMARY | 1 | id | A | 520515 | NULL | NULL | | BTREE | |
| playsms_tblSMSOutgoing_queue_dst | 1 | queue_id | 1 | queue_id | A | 130128 | NULL | NULL | | BTREE | |
| playsms_tblSMSOutgoing_queue_dst | 1 | idx_pid | 1 | pid | A | 520515 | NULL | NULL | YES | BTREE | |
±---------------------------------±-----------±---------±-------------±------------±----------±------------±---------±-------±-----±-----------±--------+
3 rows in set (0.02 sec)
Might be worth putting these in a release if they are helpful.
Sure, Ill add them soon
Anton