How to partition MySQL table with VARCHAR field

Other Questions MySQL
1

I’m working with a massive database table that has about 500 million records. The table structure looks like this:

  • record_id - BIGINT - Auto increment primary key
  • created_date - DATETIME - Around 1.5M records daily, old data gets purged after 12 months
  • user_identifier - VARCHAR(60) - Contains user identification
  • session_id - INT - Session identifier number
  • begin_time - INT - Start timestamp in epoch format
  • finish_time - INT - End timestamp in epoch format
  • Additional fields not important for this issue

I have a unique constraint on user_identifier + session_id combination, plus an index on the date field.

The table is getting too big so I want to add partitioning. Since most queries filter by date, using date-based partitioning makes sense. But since the date isn’t part of my unique constraint, this approach won’t work.

I’m considering two approaches:

First approach: Range partitioning with triggers
Maybe I could drop the unique constraint and use a BEFORE INSERT trigger instead. The trigger would look for existing records from the previous day with matching identifiers, update the end time if found, and skip the insert.

Not sure if MySQL allows updating the same table from within a trigger or canceling the insert operation.

Second approach: Hash partitioning on VARCHAR
Alternatively, I could use hash partitioning on the user_identifier field. But I can’t find examples of converting VARCHAR to INTEGER for hash partitioning.

I tried this but got an error:

ALTER TABLE user_sessions
PARTITION BY HASH (CONV(SUBSTRING(MD5(user_identifier), 1, 8), 16, 10))
PARTITIONS 16

MySQL says the partition function isn’t allowed. What’s the right way to handle this situation?

2

honestly, just add created_date to your unique constraint. change it to (user_identifier, session_id, DATE(created_date)) and you can partition by date range like normal. yeah, it tweaks the constraint a bit, but if users can’t have multiple sessions per day anyway, it won’t break anything. way simpler than dealing with computed columns or triggers.

3

Had the same issue with a huge transaction table last year. MySQL won’t let you use CONV() or SUBSTRING() in partition expressions - it’s super restrictive about functions.

I solved it by adding a computed column just for partitioning. Create a partition_key column that’s calculated from your VARCHAR field using CRC32() (which MySQL actually allows):

ALTER TABLE user_sessions ADD COLUMN partition_key INT AS (CRC32(user_identifier)) STORED;
ALTER TABLE user_sessions PARTITION BY HASH (partition_key) PARTITIONS 16;

This keeps your unique constraint intact while getting you the partitioning benefits. CRC32 distributes values pretty well across partitions.

One heads up - you’ll need to include this column in your unique constraint when you recreate it after partitioning.

Skip the trigger approach. MySQL’s trigger restrictions make it a nightmare to maintain. The computed column method is way cleaner.