Multiple batch updates

[myrddinwylt]

Is there a faster way to do this ?

 

UPDATE `cdrtemp`.`taqua` SET `TotalCost`=(0.45 * `TotalTime`), `Location`='Maldives - Intl Premium Services' WHERE `OrigDestDigits` LIKE '0119600900900%' AND `TotalCost` IS NULL;
UPDATE `cdrtemp`.`taqua` SET `TotalCost`=(0.35 * `TotalTime`), `Location`='Switzerland - Mobile' WHERE `OrigDestDigits` LIKE '0114186077357%' AND `TotalCost` IS NULL;
UPDATE `cdrtemp`.`taqua` SET `TotalCost`=(0.17 * `TotalTime`), `Location`='Sweden - Mobile' WHERE `OrigDestDigits` LIKE '011467672673%' AND `TotalCost` IS NULL;
UPDATE `cdrtemp`.`taqua` SET `TotalCost`=(0.35 * `TotalTime`), `Location`='Switzerland - Mobile' WHERE `OrigDestDigits` LIKE '011418607731%' AND `TotalCost` IS NULL;
UPDATE `cdrtemp`.`taqua` SET `TotalCost`=(0.35 * `TotalTime`), `Location`='Switzerland - Mobile' WHERE `OrigDestDigits` LIKE '011418607730%' AND `TotalCost` IS NULL;
UPDATE `cdrtemp`.`taqua` SET `TotalCost`=(0.65 * `TotalTime`), `Location`='Bulgaria - Temp' WHERE `OrigDestDigits` LIKE '011359881540%' AND `TotalCost` IS NULL;
UPDATE `cdrtemp`.`taqua` SET `TotalCost`=(0.3 * `TotalTime`), `Location`='Azerbaijan - Mobile' WHERE `OrigDestDigits` LIKE '01199460540%' AND `TotalCost` IS NULL;
UPDATE `cdrtemp`.`taqua` SET `TotalCost`=(0.3 * `TotalTime`), `Location`='United Kingdom - 44118343' WHERE `OrigDestDigits` LIKE '01144118343%' AND `TotalCost` IS NULL;
UPDATE `cdrtemp`.`taqua` SET `TotalCost`=(0.3 * `TotalTime`), `Location`='United Kingdom - 44118181' WHERE `OrigDestDigits` LIKE '01144118181%' AND `TotalCost` IS NULL;
UPDATE `cdrtemp`.`taqua` SET `TotalCost`=(0.3 * `TotalTime`), `Location`='United Kingdom - 44118178' WHERE `OrigDestDigits` LIKE '01144118178%' AND `TotalCost` IS NULL;
UPDATE `cdrtemp`.`taqua` SET `TotalCost`=(0.3 * `TotalTime`), `Location`='United Kingdom - 44118006' WHERE `OrigDestDigits` LIKE '01144118006%' AND `TotalCost` IS NULL;
UPDATE `cdrtemp`.`taqua` SET `TotalCost`=(0.3 * `TotalTime`), `Location`='United Kingdom - 44118005' WHERE `OrigDestDigits` LIKE '01144118005%' AND `TotalCost` IS NULL;
UPDATE `cdrtemp`.`taqua` SET `TotalCost`=(0.26 * `TotalTime`), `Location`='Austria - Mobile' WHERE `OrigDestDigits` LIKE '01143650040%' AND `TotalCost` IS NULL;
UPDATE `cdrtemp`.`taqua` SET `TotalCost`=(0.26 * `TotalTime`), `Location`='Austria - Mobile' WHERE `OrigDestDigits` LIKE '01143650035%' AND `TotalCost` IS NULL;
UPDATE `cdrtemp`.`taqua` SET `TotalCost`=(0.26 * `TotalTime`), `Location`='Austria - Mobile' WHERE `OrigDestDigits` LIKE '01143650020%' AND `TotalCost` IS NULL;
UPDATE `cdrtemp`.`taqua` SET `TotalCost`=(0.35 * `TotalTime`), `Location`='Switzerland - Mobile' WHERE `OrigDestDigits` LIKE '01141860777%' AND `TotalCost` IS NULL;
UPDATE `cdrtemp`.`taqua` SET `TotalCost`=(0.35 * `TotalTime`), `Location`='Switzerland - Mobile' WHERE `OrigDestDigits` LIKE '01141860774%' AND `TotalCost` IS NULL;
UPDATE `cdrtemp`.`taqua` SET `TotalCost`=(0.35 * `TotalTime`), `Location`='Switzerland - Mobile' WHERE `OrigDestDigits` LIKE '01141860772%' AND `TotalCost` IS NULL;
UPDATE `cdrtemp`.`taqua` SET `TotalCost`=(0.4 * `TotalTime`), `Location`='Switzerland - 41773112' WHERE `OrigDestDigits` LIKE '01141773112%' AND `TotalCost` IS NULL;
UPDATE `cdrtemp`.`taqua` SET `TotalCost`=(0.25 * `TotalTime`), `Location`='Latvia - Mobile' WHERE `OrigDestDigits` LIKE '01137168502%' AND `TotalCost` IS NULL;
UPDATE `cdrtemp`.`taqua` SET `TotalCost`=(0.25 * `TotalTime`), `Location`='Latvia - Mobile' WHERE `OrigDestDigits` LIKE '01137168501%' AND `TotalCost` IS NULL;
UPDATE `cdrtemp`.`taqua` SET `TotalCost`=(0.65 * `TotalTime`), `Location`='Bulgaria - Temp' WHERE `OrigDestDigits` LIKE '01135988297%' AND `TotalCost` IS NULL;
UPDATE `cdrtemp`.`taqua` SET `TotalCost`=(0.12 * `TotalTime`), `Location`='Cyprus - Mobile' WHERE `OrigDestDigits` LIKE '01135712399%' AND `TotalCost` IS NULL;
UPDATE `cdrtemp`.`taqua` SET `TotalCost`=(0.12 * `TotalTime`), `Location`='Cyprus - Mobile' WHERE `OrigDestDigits` LIKE '01135712397%' AND `TotalCost` IS NULL;
UPDATE `cdrtemp`.`taqua` SET `TotalCost`=(0.12 * `TotalTime`), `Location`='Cyprus - Mobile' WHERE `OrigDestDigits` LIKE '01135712196%' AND `TotalCost` IS NULL;
UPDATE `cdrtemp`.`taqua` SET `TotalCost`=(0.55 * `TotalTime`), `Location`='Mayotte - Mobile' WHERE `OrigDestDigits` LIKE '01126263969%' AND `TotalCost` IS NULL;
UPDATE `cdrtemp`.`taqua` SET `TotalCost`=(0.55 * `TotalTime`), `Location`='Mayotte - Mobile' WHERE `OrigDestDigits` LIKE '01126263968%' AND `TotalCost` IS NULL;
UPDATE `cdrtemp`.`taqua` SET `TotalCost`=(0.55 * `TotalTime`), `Location`='Mayotte - Mobile' WHERE `OrigDestDigits` LIKE '01126263967%' AND `TotalCost` IS NULL;
UPDATE `cdrtemp`.`taqua` SET `TotalCost`=(0.55 * `TotalTime`), `Location`='Mayotte - Mobile' WHERE `OrigDestDigits` LIKE '01126263966%' AND `TotalCost` IS NULL;
UPDATE `cdrtemp`.`taqua` SET `TotalCost`=(0.55 * `TotalTime`), `Location`='Mayotte - Mobile' WHERE `OrigDestDigits` LIKE '01126263965%' AND `TotalCost` IS NULL;
UPDATE `cdrtemp`.`taqua` SET `TotalCost`=(0.55 * `TotalTime`), `Location`='Mayotte - Mobile' WHERE `OrigDestDigits` LIKE '01126263961%' AND `TotalCost` IS NULL;
UPDATE `cdrtemp`.`taqua` SET `TotalCost`=(0.55 * `TotalTime`), `Location`='Mayotte - Mobile' WHERE `OrigDestDigits` LIKE '01126263960%' AND `TotalCost` IS NULL;
UPDATE `cdrtemp`.`taqua` SET `TotalCost`=(0.55 * `TotalTime`), `Location`='Mayotte - Mobile' WHERE `OrigDestDigits` LIKE '01126263940%' AND `TotalCost` IS NULL;
UPDATE `cdrtemp`.`taqua` SET `TotalCost`=(0.55 * `TotalTime`), `Location`='Mayotte - Mobile' WHERE `OrigDestDigits` LIKE '01126263919%' AND `TotalCost` IS NULL;
UPDATE `cdrtemp`.`taqua` SET `TotalCost`=(0.55 * `TotalTime`), `Location`='Mayotte - Mobile' WHERE `OrigDestDigits` LIKE '01126263911%' AND `TotalCost` IS NULL;
UPDATE `cdrtemp`.`taqua` SET `TotalCost`=(0.55 * `TotalTime`), `Location`='Mayotte - Mobile' WHERE `OrigDestDigits` LIKE '01126263910%' AND `TotalCost` IS NULL;
UPDATE `cdrtemp`.`taqua` SET `TotalCost`=(0.55 * `TotalTime`), `Location`='Mayotte - Mobile' WHERE `OrigDestDigits` LIKE '01126263909%' AND `TotalCost` IS NULL;
UPDATE `cdrtemp`.`taqua` SET `TotalCost`=(0.55 * `TotalTime`), `Location`='Mayotte - Mobile' WHERE `OrigDestDigits` LIKE '01126263900%' AND `TotalCost` IS NULL;
UPDATE `cdrtemp`.`taqua` SET `TotalCost`=(0.55 * `TotalTime`), `Location`='Mayotte - Mobile' WHERE `OrigDestDigits` LIKE '01126226969%' AND `TotalCost` IS NULL;
UPDATE `cdrtemp`.`taqua` SET `TotalCost`=(0.55 * `TotalTime`), `Location`='Mayotte - Mobile' WHERE `OrigDestDigits` LIKE '01126226968%' AND `TotalCost` IS NULL;
UPDATE `cdrtemp`.`taqua` SET `TotalCost`=(0.55 * `TotalTime`), `Location`='Mayotte - Mobile' WHERE `OrigDestDigits` LIKE '01126226967%' AND `TotalCost` IS NULL;
UPDATE `cdrtemp`.`taqua` SET `TotalCost`=(0.55 * `TotalTime`), `Location`='Mayotte - Mobile' WHERE `OrigDestDigits` LIKE '01126226966%' AND `TotalCost` IS NULL;
UPDATE `cdrtemp`.`taqua` SET `TotalCost`=(0.55 * `TotalTime`), `Location`='Mayotte - Mobile' WHERE `OrigDestDigits` LIKE '01126226965%' AND `TotalCost` IS NULL;
UPDATE `cdrtemp`.`taqua` SET `TotalCost`=(0.3 * `TotalTime`), `Location`='Mayotte - Other' WHERE `OrigDestDigits` LIKE '01126226964%' AND `TotalCost` IS NULL;
UPDATE `cdrtemp`.`taqua` SET `TotalCost`=(0.3 * `TotalTime`), `Location`='Mayotte - Other' WHERE `OrigDestDigits` LIKE '01126226963%' AND `TotalCost` IS NULL;
UPDATE `cdrtemp`.`taqua` SET `TotalCost`=(0.3 * `TotalTime`), `Location`='Mayotte - Other' WHERE `OrigDestDigits` LIKE '01126226962%' AND `TotalCost` IS NULL;
UPDATE `cdrtemp`.`taqua` SET `TotalCost`=(0.3 * `TotalTime`), `Location`='Mayotte - Other' WHERE `OrigDestDigits` LIKE '01126226961%' AND `TotalCost` IS NULL;
UPDATE `cdrtemp`.`taqua` SET `TotalCost`=(0.55 * `TotalTime`), `Location`='Mayotte - Mobile' WHERE `OrigDestDigits` LIKE '01126226919%' AND `TotalCost` IS NULL;
UPDATE `cdrtemp`.`taqua` SET `TotalCost`=(0.55 * `TotalTime`), `Location`='Mayotte - Mobile' WHERE `OrigDestDigits` LIKE '01126226911%' AND `TotalCost` IS NULL;
UPDATE `cdrtemp`.`taqua` SET `TotalCost`=(0.55 * `TotalTime`), `Location`='Mayotte - Mobile' WHERE `OrigDestDigits` LIKE '01126226910%' AND `TotalCost` IS NULL;
UPDATE `cdrtemp`.`taqua` SET `TotalCost`=(0.14 * `TotalTime`), `Location`='Israel - Mobile' WHERE `OrigDestDigits` LIKE '0119721535%' AND `TotalCost` IS NULL;
UPDATE `cdrtemp`.`taqua` SET `TotalCost`=(0.14 * `TotalTime`), `Location`='Israel - Mobile' WHERE `OrigDestDigits` LIKE '0119721515%' AND `TotalCost` IS NULL;
UPDATE `cdrtemp`.`taqua` SET `TotalCost`=(0.24 * `TotalTime`), `Location`='Honduras - Mobile' WHERE `OrigDestDigits` LIKE '0115047252%' AND `TotalCost` IS NULL;
UPDATE `cdrtemp`.`taqua` SET `TotalCost`=(0.24 * `TotalTime`), `Location`='Honduras - Mobile' WHERE `OrigDestDigits` LIKE '0115047251%' AND `TotalCost` IS NULL;
UPDATE `cdrtemp`.`taqua` SET `TotalCost`=(0.24 * `TotalTime`), `Location`='Honduras - Mobile' WHERE `OrigDestDigits` LIKE '0115047250%' AND `TotalCost` IS NULL;
UPDATE `cdrtemp`.`taqua` SET `TotalCost`=(0.24 * `TotalTime`), `Location`='Honduras - Mobile' WHERE `OrigDestDigits` LIKE '0115047240%' AND `TotalCost` IS NULL;
UPDATE `cdrtemp`.`taqua` SET `TotalCost`=(0.24 * `TotalTime`), `Location`='Honduras - Mobile' WHERE `OrigDestDigits` LIKE '0115047219%' AND `TotalCost` IS NULL;
UPDATE `cdrtemp`.`taqua` SET `TotalCost`=(0.24 * `TotalTime`), `Location`='Honduras - Mobile' WHERE `OrigDestDigits` LIKE '0115047218%' AND `TotalCost` IS NULL;
UPDATE `cdrtemp`.`taqua` SET `TotalCost`=(0.24 * `TotalTime`), `Location`='Honduras - Mobile' WHERE `OrigDestDigits` LIKE '0115047217%' AND `TotalCost` IS NULL;
UPDATE `cdrtemp`.`taqua` SET `TotalCost`=(0.24 * `TotalTime`), `Location`='Honduras - Mobile' WHERE `OrigDestDigits` LIKE '0115047216%' AND `TotalCost` IS NULL;
UPDATE `cdrtemp`.`taqua` SET `TotalCost`=(0.24 * `TotalTime`), `Location`='Honduras - Mobile' WHERE `OrigDestDigits` LIKE '0115047215%' AND `TotalCost` IS NULL;
UPDATE `cdrtemp`.`taqua` SET `TotalCost`=(0.24 * `TotalTime`), `Location`='Honduras - Mobile' WHERE `OrigDestDigits` LIKE '0115047214%' AND `TotalCost` IS NULL;
UPDATE `cdrtemp`.`taqua` SET `TotalCost`=(0.19 * `TotalTime`), `Location`='United Kingdom - O2 Mobile' WHERE `OrigDestDigits` LIKE '0114478937%' AND `TotalCost` IS NULL;
UPDATE `cdrtemp`.`taqua` SET `TotalCost`=(0.19 * `TotalTime`), `Location`='United Kingdom - O2 Mobile' WHERE `OrigDestDigits` LIKE '0114478936%' AND `TotalCost` IS NULL;
UPDATE `cdrtemp`.`taqua` SET `TotalCost`=(0.19 * `TotalTime`), `Location`='United Kingdom - O2 Mobile' WHERE `OrigDestDigits` LIKE '0114478935%' AND `TotalCost` IS NULL;
UPDATE `cdrtemp`.`taqua` SET `TotalCost`=(0.19 * `TotalTime`), `Location`='United Kingdom - O2 Mobile' WHERE `OrigDestDigits` LIKE '0114478934%' AND `TotalCost` IS NULL;
UPDATE `cdrtemp`.`taqua` SET `TotalCost`=(0.19 * `TotalTime`), `Location`='United Kingdom - O2 Mobile' WHERE `OrigDestDigits` LIKE '0114478932%' AND `TotalCost` IS NULL;

 

There are approximately 4000 UPDATE statements which are generated based off another table by the length of the dialed code DESC ...  so "0114478932" would come before "011447893"  for example.  If "011447893" came before "0114478932", then the UPDATE would be incorrect as the cost of "011447893" and "0114478932" could be different.

 

Currently, this process takes about 3 hours where the recordset for `cdrtemp`.`taqua` is around 500,000 records.

[fenway]

Well, does that query use an index?

[myrddinwylt]

Yes, of course.

[fenway]

Are you certain?  Post explain output for the equivalent selects.

[myrddinwylt]

The equivalent selects for the above updates entail various records.  They are indexed on 2 columns.

 

UniqueID =  This column is of type INT AUTO_INCREMENT INDEX  (primary key)

OrigDialedDigits = The column that is being compared with the "LIKE" statements. There is no other way to do this comparison, and must be done in descending order based on the length of the CODE.

 

 

[fenway]

That's all well and good -- but until you post the equivalent select statements, and their EXPLAIN output, I can't help you.