Mysql – Using TEXT or even MEDIUMTEXT for all text columns in a MySQL table

MySQLperformance

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?

Best Answer

Drawbacks of what you propose:

  • Temp tables (for complex SELECTs) cannot use MEMORY, which is faster than the fallback of MyISAM.
  • Storing numeric 'values' VARCHAR makes it difficult or slow to test for.
  • Key-value schema leads to ugly JOINs. And they are inefficient.
  • In InnoDB, depending on the ROW_FORMAT, TEXT fields may be stored in other blocks. This may slow you down (INSERTs and SELECTs) due to extra disk hits.

Yes/no -- the ALTER TABLE to change the width of a VARCHAR may be costly. In some very recent version, increasing the size of a VARCHAR became virtually free. (There may be more caveats.)

Related Question