More Group Sites
Education Books
School Rankings
Jobless Net
Better Home
Enviro++
更好教育论坛
Welcome Guest! To enable all features please Login or Register.

Notification

Icon
Error

Share
Options
View
Go to last post Go to first unread
hong  
#1 Posted : Thursday, July 30, 2015 9:57:45 PM(UTC)
hong

Rank: Administration

Reputation:

Groups: AcademicCoachingSchool, admin, Administration, BookSeller, CatholicSchool, CoachingAdult, CoachingProfessional, CoachingSports, ExtraCurriculumCoaching, IndependentSchool, Moderator, MusicTeacher, PrivateSchool, PublicSchool, SelectiveSchool, tutor
Joined: 11/23/2008(UTC)
Posts: 522

Adding a column to a large table in MySQL

I failed to ALTER TABLE of more than 10 million rows in MySQL using MySQL Workbench. SQL server disconnected after timing out.

I also tried COPY AND RENAME the big table instead of ALTER TABLE without success.
a.
Code:
Create table tbl_tmp like big_table;
Alter table tbl_tmp add column ts TIMESTAMP not null;
Alter table tbl_tmp disable keys;
Insert into tbl_tmp select *,'0000-00-00 00:00:00' from big_table;
Alter table tbl_tmp enable keys;
Rename table big_table to tbl_old;
Rename table tbl_tmp to big_table;


b. http://dba.stackexchange...mn-to-big-table-in-mysql
Use MySQL Workbench script "Create Statement".
rename the table (ALTER) and create a new one with the CREATE script you get from Workbench. You can also extend that
query with the new field you need
BULK LOAD the data from the old table to the new one:
Code:
SET FOREIGN_KEY_CHECKS = 0;
SET UNIQUE_CHECKS = 0;
SET AUTOCOMMIT = 0;
INSERT INTO new_table (fieldA, fieldB, fieldC, ..., fieldN)
   SELECT fieldA, fieldB, fieldC, ..., fieldN
   FROM old_table
SET UNIQUE_CHECKS = 1;
SET FOREIGN_KEY_CHECKS = 1;
COMMIT;


Tried to avoid indexing record by record, but it was still slow.

Finally I used PhpMyAdmin to add a new column, and it was a success.

Edited by user Thursday, July 30, 2015 10:02:42 PM(UTC)  | Reason: Not specified

Sponsor
Rss Feed  Atom Feed
Users browsing this topic
Guest
Forum Jump  
You cannot post new topics in this forum.
You cannot reply to topics in this forum.
You cannot delete your posts in this forum.
You cannot edit your posts in this forum.
You cannot create polls in this forum.
You cannot vote in polls in this forum.