Update Current Row Using PHP/MySQL Loop

Question

I have a MySQL table with over 16 million rows and there is no primary key. Whenever I try to add one, my connection crashes. I have tried adding one as an auto increment in PHPMyAdmin and in command line but the connection is always lost after about 10 minutes.

What I would like to do is loop through the table's rows in PHP so I can limit the number of results and with each returned row add an auto-incremented ID number. Since the number of impacted rows would be reduced by reducing the load on the MySQL query, I won't lose my connection.

I want to do something like

SELECT * FROM MYTABLE LIMIT 1000001, 2000000;

Then, in the loop, update the current row

UPDATE (current row) SET ID='$i++'

How do I do this?

I am open to a MySQL solution as well. I just need a process that will not cause me to lose my MySQL connection

Note: the original data was given to me as a txt file. I don't know if there are duplicates but I cannot eliminate any rows. Also, no rows will be added. This table is going to be used only for querying purposes. When I have added indexes, however, there were no problems.

Nadir · Accepted Answer · 2016-03-08 12:29:30Z

0

It is easier

ALTER TABLE table_name MODIFY COLUMN id INT NOT NULL AUTO_INCREMENT PRIMARY KEY

In case you want to remove the primary key after the modification

ALTER TABLE table_name MODIFY COLUMN id INT

answered Mar 8, 2016 at 12:29

Nadir

1,81913 silver badges20 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

tcam27 Over a year ago

I tried that but my connection gets lost after about 10 minutes

LambethWalk · Accepted Answer · 2016-03-08 12:31:56Z

0

I would avoid setting primary keys manually. Much safer to let MySQL do it. You can temporarily increase the timeout in the config file:

wait_timeout = 28800
interactive_timeout = 28800

answered Mar 8, 2016 at 12:31

LambethWalk

966 bronze badges

2 Comments

tcam27 Over a year ago

I don't have access to the config file

LambethWalk Over a year ago

Depending on your setup, you may be able to change config setting with SQL commands. Please see MySQL website for documentation.

Collectives™ on Stack Overflow

Update Current Row Using PHP/MySQL Loop

2 Answers 2

1 Comment

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related