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

我有 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词本身以外的表格。

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

请您参考如下方法:

仍在寻找更好的查询/答案但是，这就是我归结为有效的方法，但是需要大约 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