Re: How should I partition this table?
> I'm still trying to get that info. (referring to SELECTs)
Is the SlowLog turned on? The worst queries will be there. You are likely to have some terrible ones already. Show me some; I will be more specific in my advice.
You could turn on the "general log" for a short period of time; that would get all the queries. "Short" because it fills up disk fast.
It sounds like all the SELECTs might be these:
WHERE Division = ?
WHERE Division = ? AND Date = ?
WHERE Division = ? AND Date BETWEEN ? AND ?
This index handles all of them 'perfectly':
INDEX(Division, Date)
And that does not matter whether it is DATE+TIME or DATETIME except that the last two cases should be phrased
WHERE Division = ?
AND DateTime >= ?
AND DateTime < ? + INTERVAL ? DAY
Where the two datetime fillins are the same starting date.
You say the queries are based on "date" -- is it InvDate? If so, you already have the best index for those queries. Well, maybe. InnoDB, if you don't have an explicit PRIMARY KEY, will pick the first UNIQUE key for the PK. However, the fields are NULLable; this may disallow using it. Can you make all of (`Division`,`InvDate`,`InvNum`,`InvLine`) "NOT NULL"? It might make a significant performance difference. And change "UNIQUE KEY" to "PRIMARY KEY". And add NOT NULL to any other fields that don't need to be NULL.
PARTITIONing buys nothing. (Well, it may be a little faster, or maybe a little slower.) (Again, I am making assumptions about the SELECTs, etc.)
And creating (and incrementally maintaining) a "summary table" with
PRIMARY KEY(Division, Date), plus counts, sums, etc as needed, would make the queries run much faster (possibly more than 10 times as fast).
> The table in question is the "Invoice Item Detail" so an insert would be for a single division, not all 36.
PARTITIONing makes no difference in performance here. At least not for single-row inserts. If they are batched inserts, tell me more.
> 46 hrs
Most ALTERs require copying the table over. It takes 46 hours to do that, whether it is adding/removing PARTITIONing, or doing other things. One thing that makes it take so long is all the secondary indexes.
Secondary key suggestions:
KEY `PluNum` (`Division`,`PLU`), -- DROP this; the next one handles it.
KEY `Div_Plu_inv` (`Division`,`PLU`,`InvNum`),
KEY `Tax` (`Division`,`Taxable`), -- low cardinality; probably never used; drop it.
KEY `Div_Date_Plu` (`Division`,`InvDate`,`PLU`) -- The UNIQUE KEY and/or Div_Plu_inv may handle what this is needed for. May not be worth keeping.
If you went with PARTITIONs, it would probably be wise to move Division to the end of each index.
The brick wall between you and the developers will _not_ lead to useful performance improvements. They _must_ be a part of the solution. You can _not_ fix the problem alone.