Altering column specification/indexes on large table

Databases
1

I’ve got a table with about two and a half million records in. One of the columns in the table is ipAddress which seems to be stored as a varchar(50). why I did that I do not know!

I understand that alter table will make a copy of the entire table. So if I changed the field to a varchar(15) it’d have a massive CPU and disk usage issue. How about if I just changed the index to length 15? Would that have to copy the whole table, or just that one index? And would using this shorter key improve efficiency?

I know it’s more efficient to store ip addresses as unsigned ints, but I don’t want to have to change too much right now if I don’t have to.

2

there is no need to change VARCHAR(50) to VARCHAR(15)

3

So a shorter index wouldn’t make queries using that index more efficient?

4

It’s not a good idea to change it to 15 anyway since we’ll probably all be switching to IPv6 addresses before long (since we’ve exhausted the IPv4 address pool), and those addresses can be longer than 15 characters :slight_smile:

Also see IPv6 - Wikipedia, the free encyclopedia

5

if the index were actually shorter, i would have to say yes

but wouldn’t the indexes actually be the same size? they contain the same (short) values

6

So the length of the index is based purely on the length of the content?
I was just checking a EXPLAIN statement, which said that the length of the key was 50. Red herring?

7

dunno… i would have to test it