Recently I am working on a project where I will need to convert client's database with millions of old Paradox/Foxpro records to MySQL and then I have to create a new Web App based on PHP/MySQL to deal with all this old records plus new way of database structuring.
When I am trying to update a customer ID from a table A to table B, I always need to wait for few hours for the server to process it. This is so annoying and waste of time.
Finally I found the answer today where actually I need to INDEX the customer ID before doing the updates. The difference is just so BIG.
With the same command issue at the following, without INDEX it will takes hours to complete BUT after INDEXING then it just need 1.45 sec for 19,638 records!!!
UPDATE tel AS t INNER JOIN contact AS c SET t.cid = c.cid WHERE t.addr_id = c.addr_id AND t.cid = 0;
Query OK, 19638 rows affected (1.45 sec)
Rows matched: 19638 Changed: 19638 Warnings: 0
So, now I just perform the update again for full data and let's see the results 😉
UPDATE tel AS t INNER JOIN contact AS c SET t.cid = c.cid WHERE t.addr_id = c.addr_id;
Query OK, 0 rows affected (3.55 sec)
Rows matched: 103484 Changed: 0 Warnings: 0
That's why I say it's fun to play with web programming now a days 😉 lol
You must be logged in to post a comment.
