How dangerous is this sql query?

Question

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:

1. 500 Nodes
2. 50 000 Nodes
3. 1 000 000 Nodes

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.

Answer 1

This is, no doubt a pretty hefty call to be making.

I assume this is to update product prices according to the latest currency exchange rates. 1,000,000 nodes is a lot but if you have several thousand hits per second that can result in several million calculations if this is done on the fly.

My only recommendation would be to set up some kind of filtering to only update "active" products. That is, products that are visible to the public. If a product makes a change from inactive to active it should gather it's appropriate price at that time.

Answer 2

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.

Answer 3

Is this an InnoDB or MyISAM table? If MyISAM, it will lock the complete table for the entire query, this will lock out all reads for a considerable amount of time.

The query itself is OK I think, but do check it with EXPLAIN to make sure you have the proper indexes.

You could also consider using vid, and update only the latest revision of your nodes.

Answer 4

c.field_product_price_zar_value = p.sell_price * 1, 

Well this part is a waste of resources, price * 1 = price. In fact since you are updating by a set amount every time, I'm not sure the query is doing what you need anyway. In general though, I would never consider updating all the prices I have on a schedule unless there is a change requiring them to change. There is nothing in your query that indcates that any change has happened so it would happen whether or not the currency value changed (and the way it is written woudl change the values even if the currency did not change). OR am I not seeing part of your process?