Re: Partitioning dilemma

[Rick James]

GUIDs are very random. If that table is 'big', this could lead to a lot of random disk I/O. (You imply that the session table is much bigger than cache.) This is important if the GUID is indexed -- it implies uncached I/O for every INSERT.

SHOW TABLE STATUS
will show how big the tables are.

Can't you add this to help with the check:
INDEX(external_trx_id)

What's the value of innodb_buffer_pool_size, and how much RAM do you have?

So far, it sounds like most of the activity is at the 'end' of your two big tables. (I assume you will switch from GUID to AUTO_INCREMENT.) This would imply that PARTITION won't buy you anything, and a simple PRIMARY KEY on the id would be quite efficient for both tables. (Plus the index on external_trx_id.)

A few hours versus 6 months -- sounds like the hot spot on Sessions is only the last 1% of the table. This should be easily cached (unless you are talking about many terabytes).

You may need another index on some kind of date -- this would help with the DELETE. Or, you could walk through the ids on the assumption that they are roughly date-ordered.