9

I have a very big table in which I am fetching data everyday and it's size is getting bigger everyday but for my software, only 6 months data is useful. So, I am planning to remove data which is older than 6 months. I know I can do it using CRON jobs but I want to use MYSQL way to delete older data automatically. I read about PARTITION also and My question is Can I do it using PARTITION or I will have to find some alternative way ?

0

3 Answers 3

9

The Event Scheduler allows to execute regular events according to a schedule. There is detailed example in my post on the Stack Overflow, you just need to change time interval value from 24 hours to 6 months.

Firstly, make sure the Event Scheduler is enabled. To enable it use

SET GLOBAL event_scheduler = ON;

After that you could crate event that will check rows creation time and delete old records. For example

CREATE EVENT cleaning ON SCHEDULE EVERY 1 MONTH ENABLE
  DO 
  DELETE FROM MyTable
  WHERE `timestamp_column` < CURRENT_TIMESTAMP - INTERVAL 6 MONTH;

If there is no column with timestamp of a row creation in your table, then you can create trigger that will insert current timestamp and inserted row identificator to auxiliary table.

CREATE TRIGGER logCreator AFTER INSERT ON MainTable
  FOR EACH ROW 
  INSERT INTO LogTable (MainID, Created) 
  VALUES(NEW.id, CURRENT_TIMESTAMP);

Then you can use this log to get keys of main table rows that was created before specific time and delete corresponding records.

delimiter |
CREATE EVENT cleaning ON SCHEDULE EVERY 1 MONTH ENABLE
  DO
  BEGIN
    DECLARE MaxTime TIMESTAMP;
    SET MaxTime = CURRENT_TIMESTAMP - INTERVAL 6 MONTH;
    DELETE FROM MainTable
    WHERE id IN (SELECT MainID FROM LogTable WHERE Created < MaxTime);
    DELETE FROM LogTable
    WHERE LogTable.Created < MaxTime;
  END |
delimiter ;
4

PARTITION BY RANGE(TO_DAYS(...)); split by weeks. Then have weekly cron task to drop one week and build a new, empty, partition. Details.

Be sure to adjust the indexes at the time you build the partitions. Any unique or primary key must include the partition key, and that should usually be added at the end.

Provide the main SELECTs and SHOW CREATE TABLE if you would like further critique.

This shows alternatives, such as walking through the PRIMARY KEY values, deleting in chunks. PARTITIONing is the best, if it applies.

5
  • I got your point, but i just want to know, will it decrease the performance of select query ? as I have read if we are creating PARTITION of a table and still loading all the partitions to select data then it will decrease performance. As I need to select data of 6 months every time and table will have to load all partitions to answer query ?
    – Jatin Seth
    Aug 31, 2016 at 5:35
  • 1
    The benefit of DROP instead of DELETE is great. The drawback on insert/select is non-existent in some cases, minor in others. If you have a date range in the WHERE, it will prune to the one partition.
    – Rick James
    Aug 31, 2016 at 15:40
  • I am agree with you mate, but in my case; I will always need to fetch 6 months data and do calculations to provide reports. So, in my case if I will do split by week then for every query it will import all the partitions to answer the query.
    – Jatin Seth
    Sep 1, 2016 at 6:27
  • 1
    They you probably need Summary tables. Generally, in Data Warehouse situations, the "Fact" table is useful only for fetching raw data items; all other fetches should be against rollups of the data. (And it is a lot faster.)
    – Rick James
    Sep 1, 2016 at 18:38
  • I would go with partitioning and drop. Dec 29, 2018 at 19:09
1

That's exactly what I do at present. I partition the table by month, and then at the start of the next month I run a MySQL scheduled event to drop the oldest one, and create a new one. In this way everything is handled internally by MySQL.

Of course it depends on your table structure as to whether or not it can be partitioned in the first place. (plus setting up the partitions initially can take some time depending on the size of the table).

See How to split a MySQL table file?

4
  • I got your point, but i just want to know, will it decrease the performance of select query ? as I have read if we are creating PARTITION of a table and still loading all the partitions to select data then it will decrease performance. As I need to select data of 6 months every time and table will have to load all partitions to answer query ?
    – Jatin Seth
    Aug 31, 2016 at 5:35
  • If you are selecting ALL 6 months of data then there may be an impact. But if you are only selecting a smaller amount that is contained within one partition (and your query used the partitioning key) then you may see an improvement as you will only be loading a subset of data. e.g. if you partition by created_time and by month, and your query is select * from table where created_time between '2016-08-05 00:00:00' and '2016-08-10 23:59:59'; it would only pull data from the August partition.
    – IGGt
    Aug 31, 2016 at 7:14
  • No mate, I will have to show reports based on that 6 months data every time, So, my query will always be SELECT * FROM table WHERE created_time between '6 months old date' and 'today';
    – Jatin Seth
    Sep 1, 2016 at 6:29
  • Are you using innodb_file_per_table for your 'large' table? In which case I'd still be tempted to look at partitioning it, so that you can automate the maintenance. Otherwise it will simply grow in size, which will itself have an impact. Another option could be to have 6 x 1 month tables which you could query in parallel? Either way there will be a knock on affect either from partitions, multiple tables, or an ever growing table size.
    – IGGt
    Sep 1, 2016 at 7:34

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.