mysql - mysql查询结果相减

Question

我正在尝试从两列中减去查询结果。

表:

 id |   word1   | lang1 |   word2   | lang2 |
----+-----------+-------+-----------+-------+
  1 | car       |   1   | car       |  15   |
  2 | table     |   1   | table     |  15   |
  3 | Chair     |   1   | cahair    |  13   |
  4 | CDplayer  |  15   | CDplayer  |   1   |
  5 | car       |   1   | car       |  13   |

我想获取语言 1 中 word1 中尚未翻译成语言 12 的所有单词。因此在本例中为 Chair

表中有 300 万行，以下查询运行需要 1 分钟:

SELECT DISTINCT word1 
FROM `translations` 
WHERE lang1 = 1 
  AND lang2 != 15 
  AND NOT IN (SELECT word1 FROM `translations` WHERE lang2 == 15) 
LIMIT 10

分别对两行进行选择非常快 0.006 秒，然后我可以使用 array_diff()在 PHP 中将它们相减，但可能有一种更简单的方法可以直接在 MySQL 中执行此操作。

Answer 1

SELECT 
    origin.word1
FROM 
    ( SELECT DISTINCT word1
      FROM tableX 
      WHERE lang1 = 1
    ) AS origin
WHERE 
    NOT EXISTS
      ( SELECT *
        FROM tableX AS trans
        WHERE trans.lang1 =  1
          AND trans.lang2 = 15
          AND trans.word1 = origin.word1
      ) ;

我会在(lang1, word1)上添加一个索引，在(lang1, lang2, word1)上添加一个索引在运行这些查询之前 .

您也可以尝试此变体(并检查两个解释计划):

SELECT DISTINCT
      word1
FROM 
      tableX AS origin
WHERE 
      lang1 = 1
  AND 
      NOT EXISTS
      ( SELECT *
        FROM tableX AS trans
        WHERE trans.lang1 =  1
          AND trans.lang2 = 15
          AND trans.word1 = origin.word1
      ) ;