I have a MyISAM table that holds a few million Messages.
I am thinking about converting it to an partition InnoDB table.
Just wondering about the best partition strategy.
I would like to do this without a code change, and the two indexes I think would be most relevant are
- ID a bigint (primary key)
- SENT_DATE datetime (index)
- FROM_ADDRESS varchar (index)
The table is mostly used for - looking up things based on date range - by a specific id - most recent row with matching FROM_ADDRESS
Is it possible to do this with SEND_DATE? I have read that partitioning needs to be done on an integer that is the primary key, is this correct?
If I partition this by id, say batches of 250000 and then search for a SENT_DATE/FROM_ADDRESS will pruning work? or will it still have to check all partitions to find it?
Thanks in advance for any help!