Very slow execution of MySql update

Question

I have this SQL:

UPDATE products pr
SET pr.product_model_id = (SELECT id FROM product_models pm WHERE pm.category_id = 1 ORDER BY rand() LIMIT 1)
  limit 200;

It took the mysql-server more then 15 seconds for these 200 records. and in the tablem there are 220,000 records.

why is that?

edit:

I have these tables which are empty, but I need to fill them with random information for testing.
True estimations shows that I will have:
80 categories
40,000 models
And, around 500,000 products

So, I've manually created:

ALL the categories.
200 models (and used sql to duplicate them to 20k).
200 products (and duplicated them to 250k)

I need them all attached.
DB tables are:

categories {id, ...}
product_models {id, category_id, ...}
products {id, product_model_id, category_id}

score 3 · Accepted Answer · edited May 23 '17 at 12:13

Although question seems to be little odd but here is a quick thought about the problem.

RAND function doesn't perform well on large data-set.

In mysql, developer try to achieve this in different ways, check these posts:
How can i optimize MySQL's ORDER BY RAND() function?

http://www.titov.net/2005/09/21/do-not-use-order-by-rand-or-how-to-get-random-rows-from-table/

One of the quick way is following(in php):

    //get the total number of row 
    $result= mysql_query("SELECT count(*) as count 
    FROM product_models pm WHERE pm.category_id = 1 ");
    $row = mysql_fetch_array($result);
    $total=$row['count'];

    //create random value from 1 to the total of rows 
    $randomvalue =rand(1,$total);


    //get the random row

    $result= mysql_query("UPDATE products pr
    SET pr.product_model_id = 
    (SELECT id FROM product_models pm 
    WHERE pm.category_id = 1 
    LIMIT $randomvalue,1)
      limit 200");

Hope this will help.

Thanks! i will add some information to the question itself to explain what i try to do — yossi, May 11 '13 at 19:44

score 1 · Answer 2 · answered May 10 '13 at 15:21

1

The 'problem' is the ORDER BY rand()

answered May 10 '13 at 15:21

moreasd

21
8

great! :-) but WHAT is the problem and how to prevent it? – yossi May 10 '13 at 15:22
2

What are you trying to achieve with ORDER BY Rand()? Updating the products table with a randomly selected model id? Seems a little odd. Also, I doubt using LIMIT 200 in the UPDATE statement is advisable. I would suggest that you try to be more explicit by using a WHERE, both in the inner SELECT and the UPDATE itself – Crwydryn May 10 '13 at 16:00

Very slow execution of MySql update

edit:

2 Answers2