The query:
UPDATE
node as n
right join content_type_product as c
on n.nid = c.nid
right join uc_products as p
on p.nid = n.nid
set
c.field_product_price_eur_value = p.sell_price * 0.0961,
c.field_product_price_zar_value = p.sell_price * 1,
c.field_product_price_gbp_value = p.sell_price * 0.0844,
c.field_product_price_usd_value = p.sell_price * 0.1305,
n.changed = now()
where n.type = 'product'
For those that haven't figured it out, this query updates all the NODES on a Drupal site to all have the latest currency. My question is, how dangerous is this query if you have:
IF this command is executed every hour?
I need to know if i should only execute this query every few hours, or if I should limit it to only updating say 500 at a time etc.
The site where this will be executed will have several node entries, and this query updated 2 rows for every 1 product. So, I'm not sure how badly this will strain the server, if I have tons of nodes.
I would suggest benchmarking this in your Test environment (you do have a test environment, right?) to approximate what sort of load your server would experience. It's very difficult to guess what sort of impact this will have without knowing more about your environment.
To improve your application, however, I would suggest storing the exchange rates in a separate table and computing them when users pull up a particular product. This way you don't have to update millions of rows when only a handful of numbers have actually changed. You could even update your exchange rates every few minutes rather than every hour, if desired.