RAND() In WHERE clause in MYSQL

Question

I've found this Blog post that shows a way to grab a random rows from a table : http://www.rndblog.com/how-to-select-random-rows-in-mysql/

I used it in a heavy delete query that I was trying to LIMIT and it worked, while also being quite fast.

DELETE table1 FROM table1 
    JOIN table2 ON table2.id = table1.salesperson_id 
    JOIN table3 ON table3.id = table2.user_id
    JOIN table4 ON table3.office_id = table4.id
WHERE table1.type = "Snapshot" 
    AND table4.id = 113 OR table4.parent_id =113
    AND RAND()<=0.001;

I don't understand how does this works. I tried googling extensively, but I found nothing regarding a RAND() in the WHERE clause that's used in this way. Neither did the docs give anything about this.

Thank you in advance.

P.S. I'm Using MYSQL 5.5

Answer 1

The expression in your WHERE clause is evaluated for every row generated by your joins.

Every time the expression calls RAND(), that function returns a different random floating-point value between 0 and 1. If this random value is equal to or less than 0.001, then that term is true. So it will be true approximately 1 out of 1000 rows, randomly. Those will be the rows it will delete. Whereas the other 999 out of 1000 rows it will skip deleting.

By the way, I would guess you want the WHERE clause to have some parentheses to enforce operator precedence:

WHERE table1.type = "Snapshot" 
    AND (table4.id = 113 OR table4.parent_id = 113)
    AND RAND()<=0.001;

Otherwise AND binds more strongly than OR, so without these parentheses, the expression will work as if you had written it this way:

WHERE (table1.type = "Snapshot" 
    AND table4.id = 113) OR (table4.parent_id =113
    AND RAND()<=0.001);

That means all the rows with type='Snapshot' AND id=113 will be deleted, whereas 1/1000 rows with parent_id=113 will be deleted, including some rows with type other than 'Snapshot'.

That's probably not what you want. You should review boolean algebra and operator precedence. See https://dev.mysql.com/doc/refman/5.7/en/operator-precedence.html