为什么添加RAND()会导致MySQL重载?

问题描述

好的，我有这个查询，该查询为我提供DISTINCT product_series，以及表中的所有其他字段:

OK I have this query which gives me DISTINCT product_series, plus all the other fields in the table:

SELECT  pi.*
FROM    (
        SELECT  DISTINCT product_series
        FROM    cart_product
        ) pd
JOIN    cart_product  pi
ON      pi.product_id =
        (
        SELECT  product_id
        FROM    cart_product po
        WHERE   product_brand = "everlon"
                AND product_type = "'.$type.'"
                AND product_available = "yes"
                AND product_price_contact = "no"
                AND product_series != ""
                AND po.product_series = pd.product_series
        ORDER BY product_price
        LIMIT 1
        ) ORDER BY product_price

这很好.我也按价格订购，所以我可以获得每个系列的起始价格.很好.

This works fine. I am also ordering by price so I can get the starting price for each series. Nice.

但是今天我的老板告诉我，此查询显示的所有产品都是metal_type 白金，他想显示随机的金属类型.因此我在ORDER BY价格之后将RAND()添加到订单中，这样我仍将获得最低价格，但价格仍为最低价格的随机金属..这是新的查询:

However today my boss told me that all the products thats are showing up from this query are of metal_type white gold And he wants to show random metal types. so I added RAND() to the order by after the ORDER BY price so that I will still get the lowest price, but a random metal in the lowest price.. here is the new query:

SELECT  pi.*
FROM    (
        SELECT  DISTINCT product_series
        FROM    cart_product
        ) pd
JOIN    cart_product  pi
ON      pi.product_id =
        (
        SELECT  product_id
        FROM    cart_product po
        WHERE   product_brand = "everlon"
                AND product_type = "'.$type.'"
                AND product_available = "yes"
                AND product_price_contact = "no"
                AND product_series != ""
                AND po.product_series = pd.product_series
        ORDER BY product_price, RAND()
        LIMIT 1
        ) ORDER BY product_price, RAND()

运行此查询时，MySQL完全关闭，并告诉我连接太多，我从主机管理员那里接到电话，问我到底做了什么.

When I run this query, MySQL completely shuts down and tells me that there are too many connections And I get a phone call from the host admin asking me what the hell I did.

我不认为这可能只是在查询中添加RAND()而引起的，我认为这只是一个巧合.修复所有问题后，我等待了几个小时，然后再次运行查询.立即...同样的问题.

I didn't believe that could be just from added RAND() to the query and I thought it had to be a coincidence. I waited a few hours after everything was fixed and ran the query again. Immediately... same issue.

那是怎么回事?因为我不知道.我的查询出问题了吗?

So what is going on? Because I have no clue. Is there something wrong with my query?

谢谢!!!!

推荐答案

对ORDER BY使用RAND()并不是一个好主意，因为它不会随着数据的增加而扩展.您可以在我对这问题.

Using RAND() for ORDER BY is not a good idea, because it does not scale as the data increases. You can see more information on it, including two alternatives you can adapt, in my answer to this question.

这篇关于为什么添加RAND()会导致MySQL重载?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持！