Geek Answers Handbook

MySQL selects a random row with JOIN from two tables

I searched for a solution to this problem for several days and did not find anything that could reduce the time taken to complete the request.

I have 2 tables:

"product_db": unique_id - [index] image url_title status - [index] "product_page" id product_unique_id - [index] page_id - [index]

I want to select a random image from product_db, where status = 'Online', and the product should be on the page id = 3

product_db has over 90,000 products, and product_page has over 150,000 rows.

Now I use the following query:

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 request takes about 2.3 seconds to run. It takes quite a while to load a web page. I tried several other queries that first return a random string from product_page using page_id = 3 and then request product_db (this reduced the time it takes), but the problem with this is that I cannot compare if the product is " online "or not.

+4
source share
2 answers

This is a sort that slows you down. Instead of randomly sorting, select 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 = if unique_id has been removed from the database. Not as random a result as ranking by rand, but the query will run much faster. If you want, you can run multiple queries with = until the result is found, and can still be quite quick than sorting all of these results.

With an explicit JOIN, this will 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
+6
source

The problem is that MySQL is not able to select a random row so that it retrieves all your products and sorts them (unnecessarily).

You can write a stored procedure that selects a random unique_id between MIN and MAX and simply tries to retrieve this product until it receives it. You can use the restriction on the attempts made.

+1
source

More articles:


All Articles