(Addressing Q1)
There are many workarounds for having bloated columns. Let's tackle that.
What is the table's ROW_FORMAT
? DYNAMIC
and COMPACT
work differently.
Do they all need to be TEXT
? Or can some be VARCHAR
with low limits? (Eg: name TEXT
--> name VARCHAR(60)
)
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 AUTO_INCREMENT
).
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.)
Please provide 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 TEXT
and 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.