CODEWITHSUNDEEP
phpmysql
tcam27
tcam27

Reputation: 105

Update Current Row in MySQL Loop

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 shell 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?

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.

Upvotes: 3

Views: 904

Answers (2)

1000111
1000111

Reputation: 13519

If you don't have duplicate rows then the following way might shed some light:

Suppose you want to update the auto incremented value for first 10000 rows.

UPDATE 
MYTABLE

INNER JOIN 

(SELECT 
*,
@rn := @rn + 1 AS row_number
FROM MYTABLE,(SELECT @rn := 0) var
ORDER BY SOME_OF_YOUR_FIELD
LIMIT 0,10000 ) t
ON t.field1 = MYTABLE.field1 AND t.field2 = MYTABLE.field2 AND .... t.fieldN = MYTABLE.fieldN

SET MYTABLE.ID = t.row_number;

For next 10000 rows just need to change two things:

  1. (SELECT @rn := 10000) var
  2. LIMIT 10000,10000

Repeat..

Note: ORDER BY SOME_OF_YOUR_FIELD is important otherwise you would get results in random order. Better create a function which might take limit,offset as parameter and do this job. Since you need to repeat the process.

Explanation:

The idea is to create a temporary table(t) having N number of rows and assigning a unique row number to each of the row. Later make an inner join between your main table MYTABLE and this temporary table t ON matching all the fields and then update the ID field of the corresponding row(in MYTABLE) with the incremented value(in this case row_number).

Another IDEA:

You may use multithreading in PHP to do this job.

  1. Create N threads.
  2. Assign each thread a non overlapping region (1 to 10000, 10001 to 20000 etc) like the above query.

Caution: The query will get slower in higher offset.

Upvotes: 0

Duane Lortie
Duane Lortie

Reputation: 1260

I suspect you are trying to use phpmyadmin to add the index. As handy as it is, it is a PHP script and is limited to the same resources as any PHP script on your server, typically 30-60 seconds run time, and a limited amount of ram.

Suggest you get the mysql query you need to add the index, then use SSH to shell in, and use command line MySQL to add your indexes.

Upvotes: 1

Related Questions