选择1个随机行并进行复杂过滤 [英] select 1 random row with complex filtering

问题描述

我有2张桌子:

第一个表users:

+-------------------------+---------+------+-----+---------+-------+
| Field                   | Type    | Null | Key | Default | Extra |
+-------------------------+---------+------+-----+---------+-------+
| id                      | int(11) | NO   | PRI | NULL    |       |
| first_name              | text    | NO   |     | NULL    |       |
| age                     | int(11) | YES  |     | NULL    |       |
| settings                | text    | YES  |     | NULL    |       |
+-------------------------+---------+------+-----+---------+-------+

第二张表proposals:

+---------+---------+------+-----+---------+----------------+
| Field   | Type    | Null | Key | Default | Extra          |
+---------+---------+------+-----+---------+----------------+
| id      | int(11) | NO   | PRI | NULL    | auto_increment |
| from_id | int(11) | NO   |     | NULL    |                |
| to_id   | int(11) | NO   |     | NULL    |                |
| status  | int(11) | NO   |     | NULL    |                |
+---------+---------+------+-----+---------+----------------+

我需要从proposals

我正在使用此sql来做(没有rand):

I'm doing it (without rand) with this sql:

SELECT DISTINCT *
FROM profiles
WHERE
    profiles.first_name IS NOT NULL
AND
NOT EXISTS (
    SELECT *
    FROM proposal
    WHERE
        proposal.to_id = profiles.id
)
LIMIT 0 , 1

性能很好:1 row in set (0.00 sec)

但是性能非常糟糕:1 row in set (1.78 sec)当我在最后添加ORDER BY RAND()时

but perfomance is very bad: 1 row in set (1.78 sec) when I add ORDER BY RAND() to the end

我在users.id中有很多漏洞，不能使用MAX(id)

I've big holes in users.id and I can't use something like MAX(id)

我尝试设置随机limit，例如:

I'he try set random limit, example:

...
LIMIT 1234 , 1;
Empty set (2.71 sec)

但是也要花很多时间:(

But it takes much time too :(

如何在性能良好的proposals.to_id中随机获取1个users.id用户?

How to get random 1 user which users.id isn't exists in proposals.to_id with good perfomance?

我认为我首先需要用rand()获取所有profiles，然后对其进行过滤，但我不知道该怎么做.

I think that I need to first get all profiles with a rand() and then filter them, but I do not know how to do it.

推荐答案

我有两个问题解决方案.

I've two problem solutions.

1)具有随机ID，来自 https://stackoverflow.com/a/4329447/2051938

1) With random id, from https://stackoverflow.com/a/4329447/2051938

SELECT *
FROM profiles AS r1
JOIN
    (SELECT CEIL(RAND() *
                     (SELECT MAX(id)
                        FROM profiles)) AS id)
        AS r2
WHERE
    r1.id >= r2.id
    AND
    r1.first_name IS NOT NULL
AND
NOT EXISTS (
    SELECT *
    FROM proposal
    WHERE
        proposal.to_id = r1.id
)
LIMIT 0 , 1

2)使用ORDER BY RAND()

SELECT *
FROM
    (
        SELECT *
        FROM profiles
        WHERE
            profiles.first_name IS NOT NULL
        ORDER BY RAND()
    ) AS users
WHERE
    NOT EXISTS (
        SELECT *
        FROM proposal
        WHERE
            proposal.to_id = users.id
    )
LIMIT 0 , 1

第一个解决方案速度更快，但存在"id漏洞"的问题，当您从结尾获得id时(用户可能比匹配结果更早结束)

First solution is faster but it've problem with "holes in id" and when you got id from the end (users may end earlier than there will be a match)

第二种解决方案速度较慢，但​​没有缺陷！

这篇关于选择1个随机行并进行复杂过滤的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！