mysql-5.5 - 从表 A 中获取不在表 B 上的随机单词？

Question

我有 2 个表如下(这是一个 phpMyAdmin 转储，这就是为什么它有 ALTER TABLE):

CREATE TABLE IF NOT EXISTS `definition` (
`id` int(10) unsigned NOT NULL,
  `page_id` int(10) unsigned NOT NULL,
  `title` varchar(255) COLLATE utf8_bin NOT NULL
) ENGINE=InnoDB  DEFAULT CHARSET=utf8 COLLATE=utf8_bin AUTO_INCREMENT=2621401 ;

CREATE TABLE IF NOT EXISTS `definition_used` (
`id` int(10) unsigned NOT NULL,
  `word` varchar(255) CHARACTER SET utf8 COLLATE utf8_bin NOT NULL,
  `ts_created` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP
) ENGINE=InnoDB  DEFAULT CHARSET=utf8 AUTO_INCREMENT=65 ;


ALTER TABLE `definition`
 ADD PRIMARY KEY (`id`), ADD UNIQUE KEY `page_id` (`page_id`), ADD KEY `title` (`title`);

ALTER TABLE `definition_used`
 ADD PRIMARY KEY (`id`), ADD KEY `word` (`word`,`ts_created`);

ALTER TABLE `definition`
MODIFY `id` int(10) unsigned NOT NULL AUTO_INCREMENT,AUTO_INCREMENT=2621401;

ALTER TABLE `definition_used`
MODIFY `id` int(10) unsigned NOT NULL AUTO_INCREMENT,AUTO_INCREMENT=65;

A SQLFiddle can be found here...

我需要从中获取一个唯一的随机单词，因为我在 definition 上有数百万条记录。表，使用 RAND直接，不是一个选项。

我确实有一个查询会得到一个随机词，就是这个:

SELECT r1.title
  FROM definition AS r1
  JOIN (SELECT (RAND() * (SELECT MAX(id)
                            FROM definition
                         )
               ) AS id
       ) AS r2
 WHERE r1.id >= r2.id
ORDER BY r1.id ASC
LIMIT 1

但是，这将根据 id 选择单词，而无需进行任何我需要的检查。现在假设它随机选择了一个 id 200 万，没有可用的词超过它给定 r1.id >= r2.id所以我没有得到任何结果，但如果它更少，它可能会有很多结果。

现在我已经归结为:

    SELECT a.title 
      FROM definition a 
 LEFT JOIN definition_used b 
        ON a.title = b.word 
     WHERE (b.id IS NULL OR  (b.ts_created = CURDATE())) AND 
           LOWER(a.title) LIKE @message
     LIMIT 1

从表 definition_used我需要确定一个 word今天没用，为了复用，所以一个 word只要 ts_created 就可以有多个条目不会与同一日期发生冲突，因此我检查:

(b.id IS NULL OR  (b.ts_created = CURDATE()))

但是，出现的单词随机化为 0，如何从列表中随机获取一个单词？

我已经看到了一些其他问题，您可以使用最大 id 来定义随机条目，但我没有从 definition 中引用单个表。表到 definition_used词本身以外的表格。

简而言之，我需要能够从可用的未使用单词中随机选择一个单词，这是我不知道该怎么做的。

Answer 1

仍在寻找更好的查询/答案但是，这就是我归结为有效的方法，但是需要大约 2 秒钟才能得到一个我认为可以进一步优化的单词，因此如果有人想试一试并优化或发布对此更好的查询，我很乐意接受它作为正确答案。

  SELECT r1.title
    FROM definition AS r1
    JOIN (SELECT (RAND() * (SELECT MAX(a.id)
                              FROM definition a 
                         LEFT JOIN definition_used b 
                                ON a.title = b.word 
                             WHERE (b.id IS NULL OR
                                    (b.ts_created = CURDATE())
                                   ) AND 
                                   LOWER(a.title) LIKE @word
                           )
                 ) AS id
         ) AS r2
   WHERE r1.id >= r2.id
ORDER BY r1.id ASC
   LIMIT 1

这是 EXPLAIN以防万一有人想看:

id  select_type     table   type    possible_keys   key     key_len     ref     rows    Extra   
1   PRIMARY     <derived2>  system  NULL    NULL    NULL    NULL    1   
1   PRIMARY     r1  range   PRIMARY     PRIMARY     4   NULL    1293640     Using where
2   DERIVED     NULL    NULL    NULL    NULL    NULL    NULL    NULL    No tables used
3   SUBQUERY    a   index   NULL    title   767     NULL    2587281     Using where; Using index
3   SUBQUERY    b   ref     word    word    767     sebot.a.title   1   Using where; Using index