优化MySQL中的自连接.提示“查询期间失去与MySQL服务器的连接”

Question

表organiseddata是以ID作为主键设计的，并使用了CaseID上的辅助索引caseindex。该表包含大约500万行和大约47000个唯一的CaseID。

ID     | CaseID  | Actor | Activity

1      | Case1   | John  | A
2      | Case1   | Sean  | C
3      | Case1   | John  | B
4      | Case2   | John  | A
5      | Case2   | Ben   | F
6      | Case2   | Brad  | B
7      | Case2   | John  | E

我有一个存储过程，它在每种情况下都执行联接。caseID作为参数currentCase传递。

SELECT T1.ID, T2.ID, (T2.ID-T1.ID) as Diff, T1.Actor FROM
(SELECT * FROM organiseddata use index(caseindex) where CaseID=currentCase) as T1
JOIN
(SELECT * FROM organiseddata use index(caseindex) where CaseID=currentCase) as T2
ON (T2.ID-T1.ID)>=2 AND T1.Actor=T2.Actor AND T1.Activity <> T2.Activity
ORDER BY (T2.ID-T1.ID) DESC;

一旦获得了T1.ID和T2.ID，我需要在中间找到中间角色

SELECT ID, Actor as IntActor from organiseddata use index(caseindex) where CaseID=currentCase AND ID>T1.ID AND ID<T2.ID;

对于找到的每个这样的ID，我需要将结果更新到RESULT_TABLE.中上表的RESULT_TABLE结构类似于(值未显示)。A、B、C、E、F的值均为双型。

Actor  | A  | B  | C  | E  | F

A      | -- | ---| -  | -- | --
B      | -- | ---| -  | -- | --
C      | -- | ---| -  | -- | --
E      | -- | ---| -  | -- | --
F      | -- | ---| -  | -- | --

使用CONCAT，我将T1.Actor和IntActor的值附加到查询(一般表示)中，如下所示。

INSERT INTO RESULT_TABLE(`T1.Actor`,`IntActor`) values ('',1)
ON DUPLICATE KEY UPDATE `IntActor`=`IntActor`+POW(0.5,Diff-2);

但是在执行了一段时间之后，我得到了“在查询期间失去了到MySQL服务器的连接”。我已经尝试过将max_allowed_packet提高到更高的值，但没有效果。

我应该在my.ini文件中更改哪些其他配置值。是否有更好的方法来执行以上的连接。

Answer 1

至于问题的联接部分，您的查询可以重写为：

SELECT T1.ID, T2.ID, (T2.ID-T1.ID) as Diff, T1.Actor 
FROM organiseddata as T1
JOIN organiseddata as T2
    ON T2.ID>=T1.ID+2 
   AND T1.Actor=T2.Actor 
   AND T1.Activity <> T2.Activity
   AND T1.CaseID = T2.CaseID
WHERE T1.CaseID=currentCase

我将对这方面的计划进行调查，然后可能会删除caseindex上的索引，并将其替换为：

CREATE INDEX ... ON organiseddata (caseindex, Actor, ID, Activity);

与原来的比较，看看它是否有帮助。