There are a lot of variables in whether PARTITION will work well. Thanks for spelling many of them out.
Be sure to do EXPLAIN PARTITIONS SELECT ... to see if it is picking the day(s) you expect it to. If, instead, it picks all the partitions, we need to rethink things.
Will the typical day(s) in a SELECT be the last day or two? Or will the requests hit random days?
Can we see the indexes?
Secondary keys can be a serious performance problem.
You should (probably) not put `valid_at` anywhere but the end of any index. Partition pruning happens before the index is used.
More comments on PARTITIONing:
http://mysql.rjweb.org/doc.php/ricksrots (skip to PARTITION section)
I prefer to have a rolling set of BY RANGE partitions, but that needs something like:
http://mysql.rjweb.org/doc.php/partitionmaint