accessing, updating a large MYSQL table advice and guidance
With the data visibility being only 7+1 days and keeping all the data for much longer, it sounds like what you really need to implement is partitioning by day. This will also make it orders of magnitude easier and quicker to purge the data you no longer wish to keep.

For archiving, you can use a 2nd replicated server that has longer partition retention. For example, the primary server can keep only 8 daily partitions, bit your scheduled event can omit partition dropping from the replication log by toggling sql_log_bin around partition dropping. The result would be that your primary server only has 8 days of data on it and is kept small and fast, while your archive replica keeps all the partitions for a much longer period, up to 8192 partitions at least, which is the limit on the number of partitions on a table. You then have 20-odd years to implement additional further archiving if you really need to keep the data forever. ;-)

In addition to that - it may be worth sharing your table definitions and slow queries - there is always the possibility that there is a more efficient way to query for the data or a better way to index, or a way to pre-aggregate for queries that have to touch thousands of rows.