Re: taking long time to ALTER the table

[Peter Brawley]

1 min to alter table with 4M rows with this many columns doesn't sound bad.

Your table is inefficient, will be a maintenance nightmare, needs considerable normalisation, eg ...

> ,invoice_date, datetime DEFAULT NULL,
> ,invoice_no, varchar(100) DEFAULT NULL,

The invoice date is in the invoices table, right? don't need it here

> ,model_id, bigint(20) DEFAULT NULL,
> ,model, varchar(50) DEFAULT NULL,
> ,brand_name, varchar(250) DEFAULT NULL,
> ,prod_name, varchar(250) DEFAULT NULL,

Model_id here, project other cols to a models table

>,sb_id, bigint(20) DEFAULT NULL,
>,sb_code, varchar(50) DEFAULT NULL,
>sb_type_code, varchar(50) DEFAULT NULL,
>,sb_name, varchar(250) DEFAULT NULL,

Ditto for "sb" cols

>ib_code, varchar(50) DEFAULT NULL,
>,ib_type_code, varchar(50) DEFAULT NULL,
>,ib_name, varchar(250) DEFAULT NULL,

Ditto for "ib" cols

> ,c_delivery_date, datetime DEFAULT NULL,

Belongs in a shipping table?

>,r_date, datetime DEFAULT NULL,
>,r_reference, varchar(100) DEFAULT NULL,
>,r_source, varchar(100) DEFAULT NULL,
>,r_industry, varchar(50) DEFAULT NULL,
>,r_application, varchar(50) DEFAULT NULL,
>,invoice_customer_reference, varchar(100) DEFAULT NULL,
>,r_invoice_no, varchar(100) DEFAULT NULL,

Project to a table for whatever "r" stands for

>,cb_code, varchar(50) DEFAULT NULL,
>,cb_type_code, varchar(50) DEFAULT NULL,
>,cb_name, varchar(250) DEFAULT NULL,

Ditto for "c" columns

>,c_address_1, varchar(100) DEFAULT NULL,
>,c_address_2, varchar(100) DEFAULT NULL,
>,c_address_3, varchar(100) DEFAULT NULL,
>,c_city, varchar(50) DEFAULT NULL,
>,c_email, varchar(100) DEFAULT NULL,
>,c_zip, varchar(10) DEFAULT NULL,
>,c_mobile_value, varchar(50) DEFAULT NULL,
>,c_mobile_ext, varchar(10) DEFAULT NULL,
>,c_work_value, varchar(50) DEFAULT NULL,
>,c_work_ext, varchar(10) DEFAULT NULL,
>,c_fax_value, varchar(50) DEFAULT NULL,
>,c_fax_ext, varchar(10) DEFAULT NULL,

Ditto for "c" columns

>,rb_code, varchar(50) DEFAULT NULL,
>,rb_type_code, varchar(50) DEFAULT NULL,
>,rb_name, varchar(250) DEFAULT NULL,
>,rb_address_1, varchar(100) DEFAULT NULL,
>,rb_address_2, varchar(100) DEFAULT NULL,
>,rb_address_3, varchar(100) DEFAULT NULL,
>,rb_city, varchar(50) DEFAULT NULL,
>,rb_email, varchar(100) DEFAULT NULL,
>,rb_zip, varchar(10) DEFAULT NULL,
>,rb_mobile_value, varchar(50) DEFAULT NULL,
>,rb_mobile_ext, varchar(10) DEFAULT NULL,
>,rb_work_value, varchar(50) DEFAULT NULL,
>,rb_work_ext, varchar(10) DEFAULT NULL,
>,rb_fax_value, varchar(50) DEFAULT NULL,
>,rb_fax_ext, varchar(10) DEFAULT NULL,

Ditto for "rb" columns

... etc for the other obvious between-column dependencies.

For a minimally efficient table design for a modern DBMS like MySQL/InnoDB, you need third normal form. This table is nowhere near that. Read about normalisation, then project those dependent column groups to where they belong and (i) you won't need to alter this table so often, (ii) when you do, it'll be quicker, (iii) your Db will perform much better.