0

I am struggling to update my massive database but my wamp/Heidisql keeps crashing due to this large update/comparisons.

I have two database tables: main table "member_all" (contains 3 million records) and child table:"mobile_results" (contains 9,000 records). The database structure of tables look like this:

Main Table ("member_all")

id int(11),
name varchar(255),
phoneWork varchar(255),
phoneMobile  varchar(255),
phoneMobileNetwork varchar(255)

Data in table looks like this:

id name      phoneWork      phoneMobile   phoneMobileNetwork 
1  bill      061090999990   0789867676    Null
3  billsaasa 06109094399990 076689867676  Null

Child Table : ("mobile_results")

id int(11) autoincrement,
phoneMobile varchar(255),
phoneMobileNetwork  varchar(255)

Data in mobile_results looks like this:

id     phoneMobile  phoneMobileNetwork
8789   0789867676   Orange     
238789 076689867676 O2

All my mobile network data for 9,000 mobile number is stored in "mobile_results" but when i try to compare both these table ,i get stuck and my wamp/Heidi sql crashes?

My question is :

How can i populate "member_all" with "phoneMobileNetwork" values from "mobile_results" efficiently?

Here are the queries i have tried:

Query 1

i divided my query using limit .This is slow and would also take 1 week to compare 9,000 records from mobile_results.

update  member_all,mobile_results 
set member_all.phoneMobileNetwork=mobile_results.phoneMobileNetwork  
where member_all.phoneMobile in  
(SELECT phoneMobile FROM mobile_results limit 1,10);

Query 2

update  member_all,mobile_results 
set member_all.phoneMobileNetwork=mobile_results.phoneMobileNetwork  
where member_all.phoneMobile in  
(SELECT phoneMobile FROM mobile_results where id <10);

Same not good for large number of records.

PLEASE help me how can i update records my "member_all" table efficiently in one go.

I would appreciate you help in this regard.

Jon Heller
  • 34,999
  • 6
  • 74
  • 132
user3489398
  • 9
  • 4
  • wow, inefficient use of space. I'd reduce the size of your varchar's...nothing to do with your question though, sorry. – ganders Jun 05 '14 at 15:49
  • What exactly does "i get stuck and my wamp/Heidi sql crashes" mean? What error do you receive? – Martin K. Jun 05 '14 at 15:51
  • @MartinK.my HeidiSQL/phpmyadmin stops responding. – user3489398 Jun 05 '14 at 15:52
  • @user3489398 :For further reference. Please make sure you format the question so that the people that are helping you see what you are asking – Arion Jun 05 '14 at 15:59
  • @Arion.Thanks arion.i will keep this thing in mind – user3489398 Jun 05 '14 at 16:01
  • 1
    You looking for a MySQL-specific or Oracle-specific answer? You seem to be referring to MySQL, but you have an 'Oracle' tag. The answer is likely to be substantially different. – Mark J. Bobak Jun 05 '14 at 16:04
  • Do you have indexes on `phoneMobile` in both tables? If you don't, you should. – GriffeyDog Jun 05 '14 at 16:14
  • Is this a one time thing or do you need to do it often? Based on that I can recommend some approaches that could be very fast. Also if it is MySQL, what engine are you using and how do you have MySQL configured? – dbschwartz Jun 06 '14 at 13:28

2 Answers2

1

Can you just try this.I think using Exist will be faster

update  member_all
set phoneMobileNetwork=
(select phoneMobileNetwork  from mobile_results where 
member_all.phoneMobile=mobile_results.phoneMobile)
WHERE EXISTS
(
select 1 from mobile_results where 
member_all.phoneMobile=mobile_results.phoneMobile);
VJ Hil
  • 904
  • 6
  • 15
0

You could try joing the tables on the update. No EXISTS, no IN. Like this:

update member_all AS ma
  JOIN mobile_results ms on ma.phoneMobile=ms.phoneMobile
  SET member_all.phoneMobileNetwork=mobile_results.phoneMobileNetwork

Reference:

Community
  • 1
  • 1
Arion
  • 31,011
  • 10
  • 70
  • 88