x

query optimization for creating a table field with huge data in mysql?

There is a database having the size almost 2-3 gb data and records. I want to add new table field in existing table with lacs of records.

My issue is that when I run simple query for creating data field then it takes long time to create this one.

I want to optimize the query to improve the performance and save the consumed time.

Please suggest me a way through which I can handle this issue

more ▼

asked Jan 31, 2014 at 11:12 AM in Default

avatar image

vishuaaryan
128 8 9 14

Is the new column populated with some type of value or just a NULL value?

Jan 31, 2014 at 01:58 PM JohnM

@johnM: the new column can be populated with null value.

Feb 01, 2014 at 04:49 AM vishuaaryan
(comments are locked)
10|1200 characters needed characters left

2 answers: sort voted first

I just looked up MySQL ALTER TABLE command.

According to that documentation, this will require you to create a copy of the table. You have to wait for that to complete. There are some exceptions which you can read in the documentation, but adding a column doesn't seem to be one of them.

This web site focuses on SQL Server and Oracle. You might get a better solution by going to a site that includes more MySQL expertise.

more ▼

answered Feb 03, 2014 at 01:39 PM

avatar image

Grant Fritchey ♦♦
137k 20 47 81

ooooh, nice. I wonder if it'd be faster to create a dummy table with the new column, insert the data and then do the rename (essentially what the alter table would do) but would allow the OP to control the stages...

Feb 03, 2014 at 02:13 PM JohnM
(comments are locked)
10|1200 characters needed characters left

I would agree with @Grant in that it should be extremely quick, especially for a 2-3GB table. I was curious if this table was highly used and thus it's taking a long time to obtain an exclusive lock on the table to make the change? Is there a way to kill all existing connections (assuming you're doing this in non-production) to the table in mysql?

more ▼

answered Feb 03, 2014 at 01:43 PM

avatar image

JohnM
14.5k 3 7 15

oops. MysQL. I didn't notice that. Maybe it works differently than SQL Server.

Feb 03, 2014 at 02:04 PM Grant Fritchey ♦♦

And it does... oooh, another reason to not like MySQL.

Feb 03, 2014 at 02:08 PM Grant Fritchey ♦♦
(comments are locked)
10|1200 characters needed characters left
Your answer
toggle preview:

Up to 2 attachments (including images) can be used with a maximum of 524.3 kB each and 1.0 MB total.

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here

By RSS:

Answers

Answers and Comments

SQL Server Central

Need long-form SQL discussion? SQLserverCentral.com is the place.

Topics:

x126
x48

asked: Jan 31, 2014 at 11:12 AM

Seen: 2050 times

Last Updated: Mar 31, 2014 at 06:16 AM

Copyright 2018 Redgate Software. Privacy Policy