CODEWITHSUNDEEP
mysqlsql
Ganesh Ghalame
Ganesh Ghalame

Reputation: 7033

How to update the huge table column?

I have a table with 9918751 records in it, please find below is data structure, 

+--------------------+--------------+------+-----+-------------------+----------------+
| Field              | Type         | Null | Key | Default           | Extra          |
+--------------------+--------------+------+-----+-------------------+----------------+
| id                 | int(11)      | NO   | PRI | NULL              | auto_increment |
| start_ip           | varchar(32)  | YES  | UNI | NULL              |                |
| end_ip             | varchar(32)  | YES  | UNI | NULL              |                |
| country            | varchar(255) | YES  |     | NULL              |                |
| region             | varchar(255) | YES  |     | NULL              |                |
| city               | varchar(255) | YES  |     | NULL              |                |
| country_conf       | varchar(255) | YES  |     | NULL              |                |
| region_conf        | varchar(255) | YES  |     | NULL              |                |
| city_conf          | varchar(255) | YES  |     | NULL              |                |
| country_code       | int(11)      | YES  |     | NULL              |                |
| region_code        | int(11)      | YES  |     | NULL              |                |
| city_code          | int(11)      | YES  |     | NULL              |                |
| two_letter_country | varchar(20)  | YES  |     | NULL              |                |
| creation_datetime  | timestamp    | NO   |     | CURRENT_TIMESTAMP |                |
+--------------------+--------------+------+-----+-------------------+----------------+

In my table the start_ip and end_ip column contains valid IPv4 IP Address(like 192.168.1.1, 12.23.34.22 etc), Now I want to update all rows(9918751) to convert the IP address to integer using INET_ATON() function. When I am running below query

update geo_location_info set start_ip = INET_ATON(start_ip);

This query was taking too much time as to update the 9918751 rows and it was not completing the update. Please let me know any alternative (store procedure ?)

Upvotes: 1

Views: 232

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1270401

The basic idea is to use a loop for the update. The problem is that the function call takes a while and queries can time out. In addition, updating almost 10 million rows puts a certain burden on the logging mechanisms for maintaining data integrity. Assuming the id is the populated incrementally, you might do this in groups of 10,000 or maybe 100,000. Unfortunately, you can't really use @Stewarts idea, but almost half of all the rows are likely to start with 1.

The basic loop is:

set @len := 10000, @i := 1;

while @i * @len < 10000000 do
     update geo_location_info
        set start_ip = INET_ATON(start_ip)
        where id between @len*@i and @len*(@i + 1) - 1;
     set @i := @i + 1;
end while;

Unfortunately, in MySQL, you need to put this into a stored procedure -- because control flow mechanisms (such as while) are only allowed in stored programs. So, something more like:

delimiter $$
create procedure do_update ()
begin
    set @len := 10000, @i := 1;
    select @max := max(id) from geo_location_info;

    while @i * @len <= @max do
         update geo_location_info
            set start_ip = INET_ATON(start_ip)
            where id between @len*@i and @len*(@i + 1) - 1;
         set @i := @i + 1;
    end while;
end $$
delimiter ;

Upvotes: 2

Related Questions