I've created a system that generically imports CSVs into a database table with metadata columns for keeping track of when a CSV record has been changed, and it allows filtering on CSV columns instead of having to read the entire file each time. Right now, the structure of the table is essentially:
id, csv_id, hash, date_modified, column_0, column_1, ..., column_63, column_64
The columns are all VARCHAR(255) because I wanted the maximum number of columns with the maximum amount of size. My database is UTF-8, so each of the column sizes has to be multiplied by three and that value has to be below 65,535. This is the size of my current schema:
255 * 65 * 3 = 49,725
255 (*3) bytes is too small because URL columns might be stored in the database that can be up to 512 bytes or more. My question is if I made the current schema of:
column_0 (TEXT), column_1 (TEXT), ..., column_63 (TEXT), column_64 (TEXT)
Is there a maximum number of TEXT columns that can be on a database table? I'm using MySQL 5.6 right now but I'll be migrating the database to Amazon Aurora. My gut says the performance of this would be horrendous, and I do need to filter against the content of the columns.
If the performance hit isn't too large for a smaller subset of columns, I'm considering a hybrid solution:
id, csv_id, hash, date_modified, data LONGTEXT, column_0 VARCHAR(512), ..., column_19 VARCHAR(512)
Where data is a JSON array and the 20 columns are indices mapped elsewhere in the system, but if changing them all to TEXT (since the columns can be as longer, theoretically), how would a design of:
id, csv_id, hash, date_modified, data LONGTEXT, column_0 TEXT, ..., column_19 TEXT
Differ?