ORDER BY RAND causing very high load

Question

I have the following query

$samecars = $this->QueryResult("SELECT * FROM carads where STATUS='1' and DEL='0' 
and TITLE !='' and IMAGE1 != '' and IMAGE1 != '-1' and PRICE BETWEEN $pricelow 
and $pricehigh order by RAND() LIMIT 0,3");

When I remove RAND() the query is executed almost instantly, if I add the RAND() it takes about 10-30 seconds.

There are about 1 million rows in the table carads. I need RAND() in order to use random displaying.

More details:

QueryResult looks like this

public function QueryResult($strQuery) {
    $this->connector();
    $query = mysqli_query($this->link, $strQuery);
    $arr = array();
    if ($query) {
        while ($result = mysqli_fetch_object($query)) {
            array_push($arr, $result);
        }
    }
    $this->close();
    return $arr;
}

ALSO I tried add in SQL the following example command

SELECT * FROM carads where STATUS='1' and DEL='0' 
and TITLE !='' and IMAGE1 != '' and IMAGE1 != '-1' and PRICE BETWEEN 8000 
and 15000 order by RAND() LIMIT 0,3"

and RAND() is highlighted red and when i execute it through #1064 - You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '"' at line 3

This is a dup as Alma pointed out. Although the referenced question deals with selecting only one row, the same principles apply in this case. — webbiedave, Jan 20 '14 at 11:06
You should check out http://stackoverflow.com/a/4329447/1961059 — Maximilian Riegler, Jan 20 '14 at 11:06
@u_mulder oups, yes indeed, i executed again, and it takes 43.6503 sec in order to execute it, any ideas? — user3140607, Jan 20 '14 at 11:07
use a search engine: http://stackoverflow.com/questions/1823306/mysql-alternatives-to-order-by-rand , http://stackoverflow.com/questions/142242/what-is-the-best-way-to-pick-a-random-row-from-a-table-in-mysql , etc — Mitch Wheat, Jan 20 '14 at 11:07
There's a book called SQL antipatterns that devotes an entire chapter to the order by rand() problem. Long story short there's no entirely ideal solution, but almost anything is better than using rand() as an ordering clause. — GordonM, Jan 20 '14 at 11:12
@DanFromGermany this would result in three random but consecutive entries... Furthermore you will need to bound-check this... — Willem Van Onsem, Jan 20 '14 at 18:09

Willem Van Onsem · Accepted Answer · 2014-01-20T11:41:26.350

3

1. Russian roulette sampling

The reason why RAND() causes problems is because all rows of the table are queried instead of for instance the first three. Furthermore sorting takes some time.

An idea to speed up processing is using Russian roulette sampling:

SELECT * FROM carads where STATUS='1' and DEL='0' and TITLE !='' and IMAGE1 != '' and IMAGE1 != '-1' and PRICE BETWEEN $pricelow and $pricehigh AND RAND() < 0.001 LIMIT 0,3

where 0.001 is fairly small. The problem with this method is that items will not be picked uniformly (first items have more chance). Furthermore the probability should depend on the size of the table (so occasionally, an administration tool should recalculate the probability). However it is a pragmatic solution to the problem.

2. PHP does (most) of the work

Since there are only three rows, you could execute the following procedure:

First query the database to find the number of rows
Generate three distinct random numbers between 0 and the number of rows (exclusive)
Query the three rows and process the data.

edited Jan 20 '14 at 11:41

answered Jan 20 '14 at 11:19

Willem Van Onsem

443,496
30
428
555

I added 0.01 and now it's processing it in 0.03 secs from 45 secs. and they are random, 10 refreshes and never encountered same car. – user3140607 Jan 20 '14 at 11:25
"10 refreshes and never encountered same car" - hardly a definitive test of uniformity! – Mitch Wheat Jan 20 '14 at 11:48
As I claim in the answer, the items are indeed not uniform: the first elements have more chance to be picked than the last ones. In engineering however, there is the law of "conservation of misery". Sometimes one needs to sacrifice some quality to make it feasible. – Willem Van Onsem Jan 20 '14 at 11:53
@CommuSoft: I was referring to user3140607: I'm aware of this technique: http://mitch-wheat.blogspot.com.au/2011/08/t-sql-generating-random-numbers-random.html – Mitch Wheat Jan 20 '14 at 11:59

score 0 · Answer 2 · edited Feb 12 '16 at 17:10

0

Another reason why the query is executing slowly is that when using an aggregate function like rand(), time functions and etc... prevents the query to be cached. So a good practice is to try and not to use these kind of functions in the SQL query and pass it with php like:

$query = "SELECT * FROM table order by " . rand();

instead of

$query = "SELECT * FROM table order by RAND()";

Hope it was useful.

Regards

edited Feb 12 '16 at 17:10

marc_s

732,580
175
1,330
1,459

answered Jan 20 '14 at 11:57

gprusiiski

430
4
18

I don't see how this might work. The resulting query becomes for instance `SELECT * FROM table order by 0.75165156`. That means that all the rows have the same order-attribute. Therefore SQL will probably yield the original order (or at least a deterministic one). – Willem Van Onsem Jan 20 '14 at 18:07

ORDER BY RAND causing very high load

2 Answers2

Linked