问题描述
几天来我一直在寻找解决这个问题的方法，但找不到任何可以减少运行查询时间的东西.
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.
我有2张桌子:
"product_db": unique_id - [index] image url_title status - [index] "product_page" id product_unique_id - [index] page_id - [index]
我要选择的是product_db中的随机图像，其中status ='Online'并且产品必须位于页面id = 3
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有超过90,000个产品，product_page有超过150000行.
product_db has over 90,000 products and product_page has over 150000 rows.
我现在使用的查询是:
此查询大约需要2.3秒才能运行.网页加载时间很长.我尝试了其他一些查询，这些查询首先从product_page返回一个具有page_id = 3的随机行，然后查询product_db(它确实减少了所需的时间)，但是问题是我无法比较产品是否为在线"
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.
推荐答案
正是这种排序使您放慢了脚步.而不是随机排序，只需选择一个随机product_db.unique_id
It's the sorting that's slowing you down. Rather than sorting by random, just select a random product_db.unique_id
在查询中，将ORDER BY RAND()替换为:
AND product_db.unique_id >= ROUND(RAND()*(SELECT MAX(unique_id) FROM product_db))
如果从数据库中删除了unique_id，请使用而不是>=来使用
.结果的随机性不如按rand排序，但是查询将执行得更快.如果愿意，可以使用=运行多个查询，直到找到一个结果为止，它可能仍然比对所有这些结果进行排序要快得多.
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.
使用显式JOIN将会是:
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
这篇关于MySQL从两个表中用JOIN选择随机行的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持！