Slow query optimization ORDER BY RAND ()

Question

I have a query that uses ORDER BY RAND(), but it takes too long and worsens as the data grows.

The query joins two tables and returns 5 random products and a random image of each product

Table 1 - Products

product_id - pk auto-inc
name 
description

Data

1 - product 1 - description
2 - product 2 - description

Table 2 - ProductImages

image_id   - pk auto-inc
product_id - fk index
filename

Data

1 - 1 - product 1 image
2 - 1 - product 1 image
3 - 1 - product 1 image
4 - 2 - product 2 image

...

I read this and this , but cannot find a way to optimize the request so that I ask for help. Thanks in advance.

Answer 1

ORDER BY RAND() , , , . , .

.

, :

" 5 ", 6 :

• ( )

• 5 OFFSET <random offset from 0 to $number_of_rows-1> LIMIT 1 (.. )

  : SELECT * FROM Products OFFSET 42 LIMIT 1 (: , )

  , .

, ORDER BY RAND().

---

, :

SELECT *
FROM (
    SELECT *
    FROM Products
    OFFSET 42 LIMIT 1
) p
JOIN ProductImages pi
ON   pi.product_id = p.id
ORDER BY RAND()
LIMIT 1

- , - ( , ), - rand().