首页 \ 问答 \ MySQL从两个表中选择带有JOIN的随机行(MySQL select random row with JOIN from two tables)

MySQL从两个表中选择带有JOIN的随机行(MySQL select random row with JOIN from two tables)

我几天来一直在寻找这个问题的解决方案,找不到任何可以减少运行查询所需的时间。

我有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中

product_db有超过90,000个产品,product_page有超过150000行。

我现在使用的查询是:

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

此查询大约需要2.3秒才能运行。 这是一个很长的时间来加载网页。 我尝试了一些其他的查询,首先从page_id = 3返回product_page中的随机行,然后查询product_db(确实减少了所需的时间),但问题是我无法比较产品是否在线“在线” 。


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.


原文:https://stackoverflow.com/questions/4209886
更新时间:2023-01-23 14:01

最满意答案

这种排序会减慢你的速度。 而不是随机排序,只需选择一个随机的product_db.unique_id

在您的查询中,将ORDER BY RAND()替换为:

AND product_db.unique_id >= ROUND(RAND()*(SELECT MAX(unique_id) FROM product_db))

如果已从数据库中删除unique_id,则使用>=而不是= 。 不像rand排序那样随机,但查询执行速度会快得多。 如果您愿意,可以使用=运行多个查询,直到找到结果,并且它仍然可能比排序所有这些结果快得多。

使用显式JOIN,它将是:

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

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

相关问答

更多

相关文章

更多

最新问答

更多
  • Apache HttpClient(4.1和更新版本):如何进行基本身份验证?(Apache HttpClient (4.1 and newer): how to do basic authentication?)
  • 复选框不适用于模态(Checkbox does not work in modal)
  • 当使用大标题无法正常工作时,iOS 11会滚动到顶部(iOS 11 scroll to top when using large titles doesn't work properly)
  • 为什么我的Arduino不能超过10?(Why can't my Arduino compare above 10?)
  • 从Lib中删除PDB文件/功能(Remove the PDB file/functionality from Lib)
  • rails-2.3.5和rack-1.0.1的来源在哪里?(Where are the sources for rails-2.3.5 and rack-1.0.1?)
  • 如何使用List <>成员不可变的类?(How to make a class with List<> member immutable?)
  • Microsoft Edge popup扩展如何获取后台页面?(Microsoft Edge popup extension how to get backgroundpage?)
  • python删除中文unicode字符串之间的空格,但不删除英文单词之间的空格(python to remove space between Chinese unicode strings but not between English words)
  • SBT:如何Dockerize一个胖罐子?(SBT: How to Dockerize a fat jar?)
  • 如何从服务器请求中保存图像的分区以便以后合并(How to save segements of image in memory from server request for merging later)
  • python这段程序中a=self.[:]是什么意思 self一般用法是啥
  • ActiveRecord :: StatementInvalid:PGError:错误:关系“指令”不存在(ActiveRecord::StatementInvalid: PGError: ERROR: relation “instructions” does not exist)
  • 虚拟机安装kail linux最低配置是多少?
  • fread()中的空字符和c中的strncpy()(Null character in fread() and strncpy() in c)
  • 从ShutdownHook中查找程序退出的原因(Find, from a ShutdownHook, why a program exits)
  • 每天在固定时间运行任务(Run a task at fixed time everyday)
  • 电脑开不了机了,开机后进了桌面,显示WINDOWS BOOT MANAGER
  • 获取字符串第n个出现的索引?(Get the index of the nth occurrence of a string?)
  • python selenium 怎么打开 Chrome 并且能设置代理,请问可以指导小弟一下吗。
  • 错误:imagecopymerge()期望参数2是资源(Error: imagecopymerge() expects parameter 2 to be resource)
  • 在我的代码中,“无法找到可安装的ISAM”错误的原因是什么?(What could be the cause of 'Could not find installable ISAM' error in my code?)
  • Access 2013 - 没有自动编号的顺序编号(Access 2013 - sequential numbering without autonumber)
  • 将带有函数的Javascript对象转换为字符串(Converting a Javascript Object with Functions into a String)
  • Java中实现封装与信息隐藏与抽象与隐藏数据的比较(Practical example Encapsulation vs Information Hiding vs Abstraction vs Data Hiding in Java)
  • 不要包必须匹配java文件所在的子目录吗?(Don't packages have to match the subdirectories the java file is in?)
  • PyCharm Python控制台中的文件路径错误(File path wrong in PyCharm Python Console)
  • 在数据框列上应用curve_fit(Apply curve_fit on dataframe columns)
  • Java中的DateTime.FromOADate()相当于什么(Java中的Datetime是Datetime的两倍)(What is the equivalent of DateTime.FromOADate() in Java (double to Datetime in Java))
  • 给定DCEL,其中双胞胎等于下一个边缘,细分可以有多少面?(Given a DCEL where the twin is equal to the next of an edge, how many faces can the subdivision have?)