Q:

Optimising SQL to concatenate random words

I have a table ("j_un2") with 6,318 random words in, the total count there will never change. The IDs in the table are gapless.
I need to generate a list of 5 random concatenated strings made up of 2 words, where the total length of the string is 8 characters long.
I already got some very useful help a few days ago via:
Selecting random words from table
My basic method is to select from the table twice, and concatenate the randomly selected words.
I have a "fld_len" column which is the length of the word.
Table structure:
CREATE TABLE `j_un2` (
  `fld_id` int(11) NOT NULL AUTO_INCREMENT,
  `fld_un` varchar(255) DEFAULT NULL,
  `fld_cat_id` int(11) DEFAULT NULL,
  `fld_len` int(2) NOT NULL,
  PRIMARY KEY (`fld_id`),
  KEY `cat` (`fld_cat_id`),
  KEY `bob` (`fld_len`,`fld_un`)
);

The table has these indexes:
Keyname     Type    Field
PRIMARY     PRIMARY fld_id
bob         INDEX   fld_len, fld_un

I found major performance issues if I did an ORDER BY RAND(). After reading on StackOverflow and here: http://www.warpconduit.net/2011/03/23/selecting-a-random-record-using-mysql-benchmark-results/
I got this query down to about 3.7 seconds:
   SELECT CONCAT(w1.fld_un, w2.fld_un) bbb
        , FLOOR(1 + RAND() * 6318) 'rand_ind'
     FROM j_un2 w1
        , j_un2 w2 
    WHERE w1.fld_len = 8 - w2.fld_len
      AND w2.fld_len < 8
      AND RAND()<(((1/6318)*10)) 
 ORDER BY rand_ind
    LIMIT 20;

This is the explain plan for the query:
id  select_type     table   type    possible_keys   key     key_len     ref     rows    Extra
1   SIMPLE          w2      range   bob             bob     4           NULL    5886    Using where; Using index; Using temporary; Using filesort
1   SIMPLE          w1      ref     bob             bob     4           func    63      Using where; Using index

The page I'd like to use this query on gets about 500k page views a month, so is quite busy (for me anyway), and if users had to wait about 4 seconds for each page refresh, they'd probably get annoyed with it.
I did also try to do the CONCAT after selecting the words, but that took 10 seconds to run:
SELECT CONCAT(word1, word2) joined
FROM
   (SELECT w1.fld_un word1, w2.fld_un word2
        , FLOOR(1 + RAND() * 6318) 'rand_ind'
     FROM j_un2 w1
        , j_un2 w2 
    WHERE w1.fld_len = 10 - w2.fld_len
      AND w2.fld_len < 10
      AND RAND()<(((1/6318)*10)) 
 ORDER BY rand_ind
    LIMIT 20) bob;

Given I'm trying to join two tables via a rather made up join method, I wondered if this query is running as fast as it ever will, or if there is any scope to speed it up?

Update 1
Actually, I think the performance hit is down to the table join mechanics, as this:
   SELECT CONCAT(w1.fld_un, w2.fld_un) bbb
     FROM j_un2 w1
        , j_un2 w2 
    WHERE w1.fld_len = 8 - w2.fld_len
      AND w2.fld_len < 8
      AND RAND()<(((1/6318)*10)) 
 ORDER BY rand()
    LIMIT 20;

Runs in the same time - e.g. doesn't make any difference to order by rand()

A:

An SQL-only solution is found here:  http://mysql.rjweb.org/doc.php/random#case_consecutive_auto_increment .  In summary it fetches one row at random based, assuming there are no gaps in the id.  Do that twice to get two rows.  No table scan is needed.  There would still need to be code to try again if need to avoid getting the same word twice.
How to make the total length of the two rows equal 8 characters?  That's an interesting twist.  For the second query:  Add an INDEX(len, id), then use the MIN and MAX id within the remaining length.  Then adapt the next 'Case' in that blog (AUTO_INCREMENT with gaps) to find the second row.  (OK, that gets messy, but hopefully I have given you some things to think about.)
That link has 8 'efficient' alternatives to ORDER BY RAND() LIMIT n.  (None are 'perfect'.)

