ansaurus

Question

Is it possible to increase a varchar's max size in MySQL without it being converted?

Answer 1

+1 A:

One alternative would be to

create a copy of the table, but with the increased VARCHAR you need the table to be. BUT NO INDEXES.

Populate the new version of the table:

INSERT INTO new_table
SELECT * FROM old_table

Apply indexes
Update application references to use new_table

Step 4 can be minimized by updating the application to use a view that points to old_table until the new_table is ready. Then you refresh the view, setting it to use new_table...

OMG Ponies 2010-09-23 00:05:35

Selecting into a new table and then indexing it would still take a long time, though - I'm after a solution which results in the change happening very quickly (to my mind, there's no reason why it can't just update the table definition, since varchars only take as much storage as needed anyway).

NullColaShip 2010-09-23 00:36:22

@NullColaShip: At ~20 million rows, nothing is going to be instantaneous. You're still going to hid the IO wall; your hard drive setup is the limiting factor.

OMG Ponies 2010-09-23 01:21:58

I absolutely understand that actually manipulating the rows is going to take time. My question was whether it can be done without manipulating the rows - I've seen suggestions that you can do some trickery involving replacing the .FRM file with a new one, which updates the table definition but doesn't touch the data, so I was wondering if it's at all possible to achieve that with SQL (so it can be done easily/safely from a PHP script)...

NullColaShip 2010-09-23 01:38:34

+1 I wouldn't mess with the internals of mysql like that, it may work it may not, it might have worked once. it may have unintended side effects down the track. OMG Ponies answer allows the indexes to be updated just once when they as are created instead of on every insert. so it will be MUCH quicker. but still not instant.

DeveloperChris 2010-09-29 00:01:14

I guess there's no way to do what I wanted, so I'll accept this answer as it's the closest that seems to be possible. It's still not going to work for me, unfortunately - but thanks anyway.

NullColaShip 2010-10-13 01:32:25

@NullColaShip: Understood, thanks, and good luck.

OMG Ponies 2010-10-13 01:45:13

ansaurus

tags:

views:

answers:

Is it possible to increase a varchar's max size in MySQL without it being converted?

related questions