How to order data randomly

Question

I have the following MySQL. I want to pull the data in random order.

Could anyonet teach me how to do it please.

$Q = $this->db->query('SELECT P.*, C.Name AS CatName
         FROM products AS P
         LEFT JOIN categories C
         ON C.id = P.category_id
         WHERE C.Name = "Front bottom"
         AND p.status = "active"
         ');

Answer 1

$Q = $this->db->query('SELECT P.*, C.Name AS CatName 
                   FROM products AS P
                   LEFT JOIN categories C
                   ON C.id = P.category_id
                   WHERE C.Name = "Front bottom"
                   AND p.status = "active"
                   ORDER BY RAND()
                   ');

you can use the RAND function of MySQL to do that, to be noted that it would perform really slowly on huge dataset (~ about 10k). MySQL would pickup a random number for each row of the table which could lead to problem if the table is huge.

A safer method would be to do a SELECT count(*) as n FROM table and to pickup a random number and do a query with LIMIT 1,n to pickup the nth row. That would work if you need only 1, or you don't care having the result in same order.

After if you really need a complete random set better to do it on server side in my opinion.

Answer 2

You can try

ORDER BY RAND()

Answer 3

The easiest way is using ORDER BY RAND(), but it's performance is miserable, especially for larger datasets (requires a random number for all matching rows).

Another way is randomly creating ids(Either in your code or using RAND() again: WHERE id in (RAND(), RAND(), RAND(), RAND()) should work, but no guarantee). This gets problematic as soon as some IDs don't exist.

Here is an interesting article on the topic.