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
最满意答案
这种排序会减慢你的速度。 而不是随机排序，只需选择一个随机的
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 1It's the sorting that's slowing you down. Rather than sorting by random, just select a random
product_db.unique_idIn 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
相关问答
更多-
这种排序会减慢你的速度。 而不是随机排序，只需选择一个随机的product_db.unique_id 在您的查询中，将ORDER BY RAND()替换为： AND product_db.unique_id >= ROUND(RAND()*(SELECT MAX(unique_id) FROM product_db)) 如果已从数据库中删除unique_id，则使用>=而不是= 。 不像rand排序那样随机，但查询执行速度会快得多。 如果您愿意，可以使用=运行多个查询，直到找到结果，并且它仍然可能比排序所 ...
-
MySQL JOIN两个表只有一行，每个条目都有一个属性(MySQL JOIN two tables with only one row and an attribute for each entry)[2022-04-09]
SELECT p.id, p.author, p.title, max(case when m.meta_key = 'name' then m.meta_value end) as author_name, max(case when m.meta_key = 'zip-code' then m.meta_value end) as author_zip, max(case when m.meta_key = 'status' then m.meta_value ... -
首先， JOIN表格。 然后使用case表达式来进行条件聚合： select t1.AID, t1.FIRST, t1.LAST, max(case when t2.TypeOFAddress = 'STREET' then t2.Address end) STREET, max(case when t2.TypeOFAddress = 'ZIPCODE' then t2.Address end) ZIPCODE, max(case when t2.TypeOFAd ...
-
分析： SELECT k_id, c_id, score FROM (SELECT k.k_id, c.c_id, m.score, row_number() over(PARTITION BY k.k_id ORDER BY NULL) rk FROM k, c, m, s WHERE k.selected = 1 AND m.score > some_value AND m.k_ ...
-
你的意思是UNION ？ SELECT * FROM table1 UNION SELECT * FROM table2 ORDER BY RAND() LIMIT 5; 更新 ：修改问题后修改后的答案： SELECT field1 FROM table1 UNION SELECT field2 FROM table2 ORDER BY RAND() LIMIT 5; 据我所知，你只需要每个表中的一个字段。 如果你需要几个，你可以列出它们：field2，field2，...只要两个SELECT中的字段数 ...
-
即使只有一个表可能有该行，MySQL也会从两个表中选择行(MySQL select row from two tables even though only one table may have the row)[2022-04-12]
你可以使用union，但你需要指定列（col1，col2，..）这两个列表应该是相同的： SELECT col1,col2,.. FROM pages WHERE slug = 'about' UNION SELECT col1,col2,.. FROM posts WHERE slug = 'about' You can use union but you need to specify columns (col1,col2,..) the two list should be ident ... -
你可以做你想做的 - 可能 - 与相关的子查询： CREATE VIEW `data_entry` AS SELECT s.`longitude` AS longitude, s.`latitude` AS latitude, (SELECT cnp.`name` FROM companiesornaturalperson cnp WHERE s.contractor = cnp.idCompaniesO ...
-
你需要有适当的连接条件，现在你正在进行交叉连接，然后具有where条件。 SELECT cf.client_id, f.file_name, fc.category_name FROM client_files cf LEFT JOIN files f ON cf.file_id = f.ID LEFT JOIN file_categories fc ON fc.ID = f.file_category_id WHERE cf.client_id = 1; You need t ...
-
如何使用这个随机解决方案： SELECT TOP 1 * FROM urls WHERE (SELECT COUNT(*) FROM urlinfo WHERE urlid = urls.urlid) = 0 ORDER BY NEWID() How about working from this random solution: SELECT TOP 1 * FROM urls WHERE (SELECT COUNT(*) FROM urlinfo WHERE urlid = urls.urlid) ...
-
MySQL：选择多个随机行的最有效方法是什么(MySQL: what's the most efficient way to select multiple random rows)[2022-06-23]
将您的RAND（）调用添加到ORDER BY子句中应该允许您忽略该ID。 尝试这个： SELECT * FROM table WHERE ... ORDER BY RAND() LIMIT 3; 在指出性能问题后，您最好的选择可能是这些方面（使用PHP）： $result = PDO:query('SELECT MAX(id) FROM table'); $max = $result->fetchColumn(); $ids = array(); $rows = 5; for ($i = ...