0

So, I need to add a new column of type tinyint to a database table with millions of records. Possible values for the column are 1 or 0. The default value I would want is 1. But, would setting a default value affect the database performance while the alter table query is running? Or is it better not to set any default value and then backfill by writing a script that backfills the column in batches?

1

When adding a TINYINT column:

  • If specified as NULL: 1-bit NULL set; 1 byte is allocated or not allocated (depending on Engine and ROW_FORMAT)
  • NOT NULL: no bit for nullness; 1 byte allocated and filled in with the specified DEFAULT (or the default DEFAULT, which is 0 for TINYINT).

If the TINYINT is to be only a true/false flag, then it does not help to make it NULL.

ALTER TABLE takes time and may impact other processes. Is the ALTER TABLE what you are worried about?

Bottom line: The ALTER needs to fill in the byte, so you may as well let it do all the work for you.

0

Realistically, the best way to know is to run the SQL in a test environment and measure the results.

I would assume using a default would cause the alter table to run slower as it has an impact on inserts. When you perform an alter table in MySQL on MyISAM, it creates a new table underneath with the new column and inserts the data into it and swaps them out.

So if it slows inserts initially and speeds selects, you'll see a similar performance. It could impact bulk load performance however, but you may prefer to break that load into chunks instead. I don't believe the alter table is using a bulk command for this and as such, would not reap those benefits.

What would be best is to create a new table with the additional column, insert the records from the original table, then rename them. I have also read using Percona schema toolkit is another good method to use here.

StackOverflow and MySQL references:

SO users on altering columns.

Percona suggestion.

Another Percona and other tool reference.

MySQL reference.

  • Do any of those references disagree with my answer? – Rick James Feb 18 '18 at 15:08
  • Your 3rd link is for a SQL Server question and the 6th about Postgres. Not really relevant to this question. – ypercubeᵀᴹ Feb 18 '18 at 16:16
  • The key element to the question is whether the default value will create a performance impact on the alter table. Both of us agree that the default could have later impacts on performance and the alter is already performing work, so it may as well fill in the byte. – Shaulinator Feb 19 '18 at 14:44

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.