TTL - Perfect Accuracy by using an insertable VIEW

One more comment regarding TTL in MySQL:

If you are looking for perfect accuracy and never want to access rows that are older than the defined TTL you can hide the table t (from my previouspost) behind a view. This view will automatically select only rows within TTL lifespan:

CREATE VIEW ttl as SELECT id, content, created_at FROM t 

WHERE created_at >= NOW() - INTERVAL 10 SECOND;

This view is insertable, so you can fully use this view and you are not distracted by the additional column "bucket".

INSERT INTO ttl VALUES (NULL, "This is a test", NULL);

You could even exclude column "created_at" from the view definition, if there was not bug #94550 . 'created_at' could be fully handled internally.

This view does not affect performance much. In my simple test it did not show any affect. Just better usability and better accuracy of TTL.

Limitations

You cannot use foreign keys with your ttl'ed table and view. This is because partitioning and foreign keys are mutually exclusive . If you need foreign keys go with the simple delete event procedure and forget about the view.

Due to bug  #94550 you have to set   explicit_defaults_for_timestamp  to  OFF  and you  always have to insert NULL into column 'created_at'.

In this whole setup the TTL is mentioned in four locations: In the partitioning definition, in the definition of the generated column 'bucket', in the cleaning event procedure and in the WHERE clause of the view. This makes it easier to screw up the setup. Make sure you use the same value everywhere. Same applies for the number of partitions in the table definition as well as the cleaning event procedure.