I'm using a PHP script to insert millions of rows into a MySQL. Each of the rows contain a diversity of columns that are very different across the rows.
Basically I'm using MySQL as a key-value pair store.
While I can make the column to auto-adjust its size according to the length of the string to be inserted on the fly, it may not be a good idea when the table has millions of rows.
For example, with 5 million rows in the table, the next insert has a column with over 5,000 characters while that column is defined as VARCHAR(127) in the table. Re-defining the column in the job would be anything but desirable. Is it? Cause' it bogs things down and it has to change that particular column of all the 5 million rows. Right?
So I'm thinking of creating each column as a TEXT or even MEDIUMTEXT column in the first place. It is after I have done all the inserts that the columns will be re-defined according to the longest value in the column.
Therefore my question is could there be any performance issues with all the columns of the table defined as TEXT / MEDIUMTEXT? Especially for large amounts of inserts?