0

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?

1

If you lump all the column_## columns into a JSON field, you can have a limit of 4GB.

The question of how much gets complicated. InnoDB has 4 ROW_FORMATs: REDUNDANT, COMPACT, DYNAMIC, COMPRESSED. I think you could have 400 columns of VARCHAR(255) or TEXT (or ...) in ROW_FORMAT=DYNAMIC. That format uses a 20-byte pointer in the record to where the actual text is stored.

If you changed the block size from the default 16KB to 32KB, you could get about 800 columns. Going to 64KB would hit the limit of 1017 columns (in 5.6.9). Changing the block size is non trivial, so I don't recommend it.

More Limits. (Hmmm... maybe I should add your question there.)

These two statements do go together: "performance of this would be horrendous" and "I do need to filter against the content of the columns". This is because DYNAMIC has an extra disk fetch for each column you need to 'filter' on. On the other hand, in some situations (short text, not too many columns, etc), the entire text of some of the columns may be stored inline.

COMPACT might actually be better. This format tries to keep the text in the record. But if the record would become bigger than about 8K, it peels off the longer cells for off-block storage. It will put the first 768 bytes of record inline. From that, you are limited to about 10 columns. (Try 12 columns, it will spit at you.)

I believe you will need innodb_file_per_table=1 for DYNAMIC.

How will you be 'filtering'?

  • Right now it's a simple query builder such as WHERE column_3 = "West" AND column_7 = "" AND column_11 = "1". There's a header name to CSV column index converter before that. – NobleUplift May 4 '16 at 15:52
  • If it is reasonable to limit the size of those columns, and if possible, declare column_11 to be INT, then you could add INDEX(column_3, column_7, column_11) and have that query be very efficient. – Rick James May 4 '16 at 18:20
  • The problem is that the table holds multiple CSVs with different definitions, so the CSV that's currently being imported and worked off of us 3, 7, and 11, but the next one I'm importing into the system is something like 2, 15, and 16. Eventually the system will have 1000 unique CSV formats that filter on different columns. That's why I'm thinking of mapping columns in the JSON object to columns in the table and using those columns as indices only for filtering, and then add an index on each of the 20-30 columns that I define. – NobleUplift May 4 '16 at 21:22
  • Are the CSVs similar in that they have approximately the same columns but in a _different_order? If so, rearrange them as you load them. – Rick James May 4 '16 at 21:41
  • No, in the examples I gave you, the first is only 11 columns long, but the second is 32 columns long. You can see how complex this got just trying to put in a second CSV lol. – NobleUplift May 4 '16 at 22:13

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.