I've been searching for a solution to this problem for a few days now and could not find anything that would reduce the time it takes to run the query.
I have 2 tables:
"product_db":
unique_id - [index]
image
url_title
status - [index]
"product_page"
id
product_unique_id - [index]
page_id - [index]
What I want to select is a random image from product_db where status = 'Online' and the product must be in page id = 3
product_db has over 90,000 products and product_page has over 150000 rows.
The query that I am using now is:
SELECT image FROM product_db a, product_page b WHERE b.page_id = 3 AND a.status = 'Online' AND a.unique_id = b.product_unique_id ORDER BY RAND() LIMIT 1
This query takes around 2.3secs to run. This is quite a long time for a web page to load. I have tried a few other queries that first returns a random row from product_page with page_id = 3 and then querying product_db (it did reduce the time it takes) but the problem with that is I cannot compare if the product is 'Online' or not.
It's the sorting that's slowing you down. Rather than sorting by random, just select a random product_db.unique_id
In your query, replace ORDER BY RAND()
with:
AND product_db.unique_id >= ROUND(RAND()*(SELECT MAX(unique_id) FROM product_db))
using >=
instead of =
in case that unique_id has been deleted from the database. Not as random a result as ordering by rand but the query will execute much faster. If you wish, you can run multiple queries with =
until a result is found and it still may be quite faster than sorting all those results.
With an explicit JOIN it would be:
SELECT product_db.image
FROM product_db
JOIN product_page ON product_db.unique_id = product_page.product_unique_id
WHERE product_page.page_id = 3
AND product_db.status = 'Online'
AND product_db.unique_id >= ROUND(RAND()*(SELECT MAX(unique_id) FROM product_db))
LIMIT 1
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With