so 53085840 finding common values of two, MySQL

Language: Layout:

-- DROP TABLE IF EXISTS test_words;
CREATE TABLE IF NOT EXISTS test_words (
  id int unsigned NOT NULL PRIMARY KEY,
  words text NOT NULL
);

-- DROP TABLE IF EXISTS test_words_unfolded;
CREATE TABLE IF NOT EXISTS test_words_unfolded (
  word varchar(10) NOT NULL,
  id int unsigned NOT NULL,
  KEY (word)
);

-- DROP TABLE IF EXISTS test_wordlist;
CREATE TABLE IF NOT EXISTS test_wordlist (
  wordid serial primary key,
  word text NOT NULL
);

-- DROP TABLE IF EXISTS test_wordids;
CREATE TABLE IF NOT EXISTS test_wordids (
  id int unsigned NOT NULL,
  wordid int unsigned NOT NULL,
  idlist text,
  KEY (id),
  KEY (wordid)
);

-- DROP TABLE IF EXISTS test_result;
CREATE TABLE IF NOT EXISTS test_result (
  id int unsigned NOT NULL PRIMARY KEY,
  words text NOT NULL
);

-- DROP TABLE IF EXISTS test_nums;
CREATE TABLE IF NOT EXISTS test_nums (n int unsigned NOT NULL PRIMARY KEY);
TRUNCATE TABLE test_nums;
INSERT INTO test_nums (n) VALUES (1),(2),(3),(4),(5),(6),(7),(8),(9),(10);

TRUNCATE TABLE test_words;
INSERT INTO test_words (id, words) VALUES
(1,'Aaa Ccc Zzz Fff Ggg'),
(2,'Ggg Qqq Rrr Ccc'),
(3,'Zzz Ggg Qqq'),
(4,'Ccc Fff'),
(5,'Ppp Ooo'),
(6,'Ooo Xxx Yyy Jjj'),
(7,'Aaa Ppp Xxx');

-- Unfolding the words
TRUNCATE TABLE test_words_unfolded;
INSERT INTO test_words_unfolded (word, id)
SELECT SUBSTRING_INDEX(SUBSTRING_INDEX(t.words,' ', nr.n),' ',-1) as word, t.id
FROM test_words AS t
JOIN test_nums AS nr
  ON CHAR_LENGTH(t.words) - CHAR_LENGTH(REPLACE(t.words,' ','')) >= nr.n - 1
 AND SUBSTRING_INDEX(SUBSTRING_INDEX(t.words,' ', nr.n),' ',-1) != '';

-- SELECT * FROM test_words_unfolded;

-- Building the word list
TRUNCATE TABLE test_wordlist;
INSERT INTO test_wordlist (word) 
SELECT DISTINCT word
FROM test_words_unfolded;

-- SELECT * FROM test_wordlist ORDER BY wordid;

TRUNCATE TABLE test_wordids;
INSERT INTO test_wordids (wordid, id)
SELECT l.wordid, u.id
FROM test_words_unfolded u
JOIN test_wordlist l ON l.word = u.word;

-- SELECT * FROM test_wordids ORDER BY id, wordid;

INSERT INTO test_wordids (id, wordid, idlist)
SELECT
q1.id, t3.wordid, GROUP_CONCAT(DISTINCT t3.id ORDER BY t3.id ASC SEPARATOR ',') as idlist
FROM 
(
  SELECT
   t1.id, t2.id as id2
  FROM test_wordids t1
  JOIN test_wordids t2 ON t1.wordid = t2.wordid
  GROUP BY t1.id, t2.id
  HAVING COUNT(*) >= 2
) q1
JOIN test_wordids t3 ON t3.id = q1.id2
GROUP BY q1.id, t3.wordid
HAVING MAX(CASE WHEN q1.id = t3.id THEN 1 ELSE 0 END) = 0;

-- SELECT * FROM test_wordids ORDER BY id, wordid;

TRUNCATE TABLE test_result;
INSERT INTO test_result (id, words)
SELECT 
 wid.id, 
 GROUP_CONCAT(DISTINCT CASE WHEN wid.idlist IS NULL THEN w.word ELSE CONCAT('[',wid.idlist,']',w.word) END ORDER BY w.word ASC SEPARATOR ' ') as words
FROM test_wordids wid
LEFT JOIN test_wordlist w ON w.wordid = wid.wordid
GROUP BY wid.id
ORDER BY wid.id;

SELECT * FROM test_result ORDER BY id, words;


absolute service time: 0,44 sec