SQL Function used to alter an existing table object.

learn more… | top users | synonyms

3
votes
1answer
2k views

Can I rename the values in a MySQL ENUM column in one query?

Suppose I have a Database table with an ENUM('value_one','value_two'). I want to change that to an ENUM('First value','Second value'). I an currently doing that as follows: ALTER TABLE `table` MODIFY ...
5
votes
3answers
5k views

ALTER TABLE on a large table with an indexed column

I have a large table with a VARCHAR(20) column, and I need to modify that to become a VARCHAR(50) column. Typically, performing an ALTER TABLE (adding a TINYINT) on this particular table takes about ...
4
votes
1answer
2k views

I need to run VACUUM FULL with no available disk space

I have one table that is taking up close to 90% of hd space on our server. I have decided to drop a few columns to free up space. But I need to return the space to the OS. The problem, though, is ...
3
votes
3answers
6k views

MySQL - fastest way to ALTER TABLE for InnoDB

I have an InnoDB table that I want to alter. The table has ~80M rows, and quit a few indices. I want to change the name of one of the columns and add a few more indices. What is the fastest way to ...
8
votes
5answers
5k views

Alter table on live production databases

How do most "popular" (MySQL, Postgres...) database system handle altering tables on live production databases (like adding, deleting or changing the type of colums)? I know the correct way is to ...
4
votes
2answers
812 views

Preordering the table by a specified column

I have a table and I am always getting the data by ordering a column. In my case, I order by datenum. However, the selection is so slow because of the ordering. Is it possible to order the table ...
4
votes
2answers
9k views

Why does simple ALTER TABLE command take so long on table with full-text index?

I have a large (~67 million rows) name-value table that has full-text indexing on the value column (DataValue). My question is: If I try to run the following sql command: ALTER TABLE VisitorData ...
3
votes
3answers
2k views

Quickly change NULL column to NOT NULL

I have a table with millions of rows and a column that allows NULL values. However no row currently has a NULL value for that column (I can verify this fairly quickly with a query). However when I ...
2
votes
2answers
2k views

Reset IDENTITY value

I have a table with an IDENTITY column. While developing I delete the rows from time to time and add them again. But the IDENTITY values always kept increasing and didn't start from 1 when I added ...
1
vote
4answers
104 views

Amount of data in a database table

I am trying to run an alter query on a table, and its giving me a Lost connection to MySQL server during query MySQL server has gone away. The query that I am running is alter table table_name ...