mysql内连接查询运行缓慢

Question

这是我的 mysql 查询:

SELECT DISTINCT a.lineid
FROM (SELECT DISTINCT tmd.lineid, a.linename
      FROM tagmodeldata tmd
           INNER JOIN
           tagline a
           ON a.documentid = tmd.documentid AND tmd.tagvalue = 3
      WHERE tmd.documentid = 926980) a
      INNER JOIN
     (SELECT DISTINCT tmd.lineid, b.linename
      FROM tagmodeldata tmd
           INNER JOIN
           tagline b
           ON b.documentid = tmd.documentid AND tmd.tagvalue IN (0 , 1)
      WHERE tmd.documentid = 926980) b
      ON b.linename = a.linename;

运行大约需要 160 秒，这对我来说太慢了。基本思想是检索那些标记值为 3 的行名的 lineid，与标记值为 0 或 1 的行名匹配。

+--+----+-------------+------------+------+---------------------------+----------------+---------+------+-------+--------------------------------+
|  | id | select_type |   table    | type |       possible_keys       |      key       | key_len | ref  | rows  |             Extra              |
+--+----+-------------+------------+------+---------------------------+----------------+---------+------+-------+--------------------------------+
|  |  1 | PRIMARY     | <derived3> | ALL  | NULL                      | NULL           | NULL    | NULL | 14760 | Using temporary                |
|  |  1 | PRIMARY     | <derived2> | ALL  | NULL                      | NULL           | NULL    | NULL | 72160 | Using where; Using join buffer |
|  |  3 | DERIVED     | b          | ref  | documentid                | documentid     |       5 |      |   593 | Using where; Using temporary   |
|  |  3 | DERIVED     | tmd        | ref  | documentid,document_index | document_index |       4 |      | 66784 | Using where                    |
|  |  2 | DERIVED     | a          | ref  | documentid                | documentid     |       5 |      |   593 | Using where; Using temporary   |
|  |  2 | DERIVED     | tmd        | ref  | documentid,document_index | document_index |       4 |      | 66784 | Using where                    |
+--+----+-------------+------------+------+---------------------------+----------------+---------+------+-------+--------------------------------+

Answer 1

您似乎想要特定文档的行同时具有 3 和 0 或 1。如果是这样，您可以只使用条件聚合。结果查询如下:

SELECT tmd.lineid
FROM tagmodeldata tmd INNER JOIN
     tagline a
     ON a.documentid = tmd.documentid AND tmd.tagvalue IN (0, 1, 3)
WHERE tmd.documentid = 926980
GROUP BY tmd.lineid
HAVING SUM(tmd.tagvalue = 3) > 0 AND
       SUM(tmd.tagvalue IN (0, 1)) > 0;

尚不清楚tagline.linename和tagline.lineid之间的关系。以上假设它们是相同的。