2

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?

3

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.)

  • I find this and this. Seems it's not something major to worry about? I'm using InnoDB. – datasn.io Jun 20 '15 at 13:18
  • 1
    TEXTs and long VARCHARs prevent use of MEMORY, leading to the slower MyISAM. Bottom line: Use VARCHAR/VARBINARY for short columns; use TEXT/BLOB when you need bigger fields. TEXT/BLOB cost something in performance. Should you "worry about it"? There are more important things to worry about. "MySQL as a key-value pair store" is the thing to worry about. – Rick James Jun 21 '15 at 5:22

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.