> PARTITION BY HASH (YEAR(invdate)) PARTITIONS 10
In my opinion, that is totally useless.
> `UPC` varchar(14) DEFAULT NULL,
> DEFAULT CHARSET=utf8
Will UPC every be anything other than ascii? It is (in some cases) better to use ascii or latin1 for non-utf8 strings.
> `ComSplDate` date DEFAULT NULL,
> `ComSplTime` time DEFAULT NULL,
Do not split DATE and TIME, unless you have some good reason. Use DATETIME instead.
> `ItemCost` double DEFAULT NULL,
Don't use FLOAT or DOUBLE for money; use DECIMAL.
> so all of the tablespace data is in ibdata1.
Oops, that makes it hard to return disk space to the OS when you change the partitioning, etc.
Back to PARTITIONing...
What are the queries? -- Picking the PARTITION key based whether it will benefit the SELECTs.
Will you 'purge' data that is 'old'? -- If so, the partition only RANGE(TO_DAYS(invDate)) and don't have more than a few dozen partitions; no subpartitions. This will facilitate purging.
More notes:
http://mysql.rjweb.org/doc.php/ricksrots#partitioning