MySQL Forums
Forum List  »  InnoDB

Re: taking long time to ALTER the table
Posted by: Peter Brawley
Date: April 14, 2016 01:40PM

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.

Options: ReplyQuote


Subject
Views
Written By
Posted
1100
April 07, 2016 07:47AM
Re: taking long time to ALTER the table
589
April 14, 2016 01:40PM


Sorry, you can't reply to this topic. It has been closed.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.