Updating and Deleting Data - Learning MySQL and MariaDB - page 168

There are flaws in the RAND() function. It's not so random and can sometimes return the
same results. So be careful about when you use it and for what purpose.
Let's start at the bottom of this UPDATE statement. The ORDER BY clause is a bit ironic
here because the order it puts the columns in is random. The LIMIT clause limits the res-
ults to only two rows. So everyone has an equal chance of being one of our two winners.
We can't be sure that the top two rows are new winners, though; we might happen to
choose the same person through a random process on different months. So weadd a
WHERE clause to update only rows in which winner_date has a value of NULL, which
indicates that the member hasn't won previously. Finally, at the top of the statement, we
set the winner_date column for the winner to the current date, using a function we'll
learn about in Chapter11 .
However, there are some problems with this SQL statement that may not be obvious.
First, the use of the RAND() function in an ORDER BY clause can be absurdly slow. You
won't notice the difference when used on a small table, but it performs poorly on an ex-
tremely large table that is used by a very active server. So, be mindful of which tables and
situations you use the RAND() function within the ORDER BY clause. Second, using the
ORDER BY clause with a LIMIT clause can cause problems if you use MySQL replica-
tion, unless you use row-based replication. This is a feature that allows you to have a mas-
ter server and slave servers that replicate or copy exactly the databases on the master.
That's an advanced topic, but I want to mention this potential problem because when you
use this combination of clauses with the UPDATE statement, you'll see a warning message
like this:
SHOW WARNINGS \G
*************************** 1. row ***************************
Level: Warning
Code: 1592
Message: Statement is not safe to log in statement format.
If you're not using MySQL replication, you can ignore this warning. If you are using it,
though, you'll have a situation in which one slave may update its data differently from the
data on the master or the other slaves — especially if you use the RAND() function (i.e.,
the slave will have different random results). Again, at this stage of learning MySQL, you
can probably ignore this warning, and can safely use these clauses and this function.
What's important is that you're aware of these potential problems and that you get of a
sense of how extensive MySQL is.