Geek Answers Handbook

Get a random word from table A that is not on table B?

I have 2 tables as follows (this is a phpMyAdmin dump, so it has 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;

Here you can find SQLFiddle ...

And I need to get a unique random word from him, since I have millions of records in the table definition, using RANDdirectly, this is not an option.

I have a query that will receive a random word, which is the following:

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

However, this will select id-based words without any checks that I need. Now suppose he chose a random one idout of 2 million, and there were no useful words that were given to him r1.id >= r2.id, so I am not getting any result, but if it were less, it could have many results.

:

    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, .

  • , , , .
+4
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
-1

More articles:


All Articles