There are many workarounds for having bloated columns. Let's tackle that.
What is the table's
COMPACT work differently.
Do they all need to be
TEXT? Or can some be
VARCHAR with low limits? (Eg:
name TEXT -->
Consider "vertical partitioning". This is where you split a table into 2 tables -- putting some columns in each table. They would have the same
PRIMARY KEY (except not both being
Do any of the columns have repeated values? If so, consider normalizing. (Eg,
country TEXT ->
country_code CHAR(2) CHARACTER SET ascii with values like 'US', 'ES', 'UK', 'IN'.)
Have you splayed an array across columns? That is a no-no. Turn that set of columns into rows in a related table. (Eg home_phone, work_phone, fax, cell.)
SHOW CREATE TABLE with the actual column names; we may have more hints. (And some of my suggestions above may be painfully obvious.)
After looking at the schema...
I see a bunch of columns that sounds like money or other numeric stuff, but is in
VARCHAR(25); consider changing to
INT or some other numeric datatype
I see about 80 each of
VARCHAR. This does stress some builtin limits in InnoDB. But they can, and need to be, worked around:
Plan A: Vertical partitioning -- break out FR, COP, PP, Customer, etc. into a few extra tables. Use
JOIN to re-gather the data.
Plan B: Throw most of the columns into a large
JSON text column. Or maybe one for FR, one for Customer, etc. Most app languages can easily parse JSON.
Since you have only one index (on
id) I guess there is no call for searching the table? A few other columns, like maybe userID and name, should be kept in their own columns, and possibly indexed.