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:

500 Nodes
50 000 Nodes
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.

The question that immediately comes to my mind is what happens if it gets interrupted... — Chris Thompson, Feb 22 '10 at 17:47
Database = MySQL (i'm running Drupal) And if it gets interrupted, it's not the end of the world, as it runs every hour. Question is, will it kill the server (i.e. use too much processing) if there are 1 000 000 nodes)? Keep in mind, it's updating products and the site could have lots and lots of products. — rockstardev, Feb 22 '10 at 17:57
Why not calculate that only when needed, when displaying your products — moi_meme, Feb 22 '10 at 21:08

score 10 · Accepted Answer · answered Feb 22 '10 at 18:03

10

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.

answered Feb 22 '10 at 18:03

Michael Moussa

4,207
5
35
53

This, 100%. You have redundancy in exchange rates in every product-- even though there will be slightly more time used in computing the actual price in the controller, you should seriously consider throwing the redundant data into a new table so you don't have to update millions of products every time the exchange rates change at all. – Platinum Azure Feb 22 '10 at 18:07
How would I similate 1 000 0000 products without actually creating 1 000 000 products? On drupal? I agree, testing it myself would be the bestway to do it, but I dont like the idea of creating so much fake data. – rockstardev Feb 22 '10 at 18:24
1

@RD: It's not fake data, it's test data. In your test environment. *Which you have, right?* – Mike Daniels Feb 22 '10 at 18:30
To make a 1000000 records: Just insert into your nodes table from your node table until you have a million records. – feihtthief Feb 22 '10 at 18:32
@Mike Daniels: I have a DEV environment on my machine, but not another extra test environment. @Feihtthief: In drupal, creating one node, means entries are created in several other tables. I think there is a module that can generate data. I will have to investigate this. – rockstardev Feb 22 '10 at 18:41
1

@RD: Do it in your DEV environment and create a mysqldump of it so you can use it later for more testing/benchmarking when needed. Actually... you can use it to test the exchange rates table when you implement it. :) – Michael Moussa Feb 22 '10 at 18:47

score 2 · Answer 2 · answered Feb 22 '10 at 18:03

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.

This gave me an idea. What if each time a product is viewed, it's pricing is updated? Hmmm. That might do the trick. — rockstardev, Feb 22 '10 at 18:25

score 1 · Answer 3 · answered Feb 22 '10 at 18:05

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.

score 1 · Answer 4 · answered Feb 22 '10 at 18:12

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?

How dangerous is this SQL query?

4 Answers4