php - 慢速依赖子查询 - 如何提高性能？

Question

我的数据库有表名事务，其中有 20000 条记录。当我运行这个查询时

SELECT T1.* FROM transactions AS T1
WHERE T1.ppno IN 
  (SELECT T2.PPNO FROM transactions AS T2 
   WHERE T2.ppno = T1.ppno 
   HAVING COUNT(T2.ppno) = $doublescount) 
 ORDER BY T1.ppno,T1.numb

运行至少需要 3 分钟。如何加速这个查询？

编辑

show create table transactions 返回为

CREATE TABLE `transactions` (
  `eidx` int(10) unsigned NOT NULL,
  `numb` int(10) unsigned NOT NULL,
  `date` date NOT NULL,
  `time` varchar(45) NOT NULL,
  `name` varchar(45) NOT NULL,
  `add1` varchar(45) NOT NULL,
  `add2` varchar(45) NOT NULL,
  `city` varchar(45) NOT NULL,
  `phno` varchar(45) NOT NULL,
  `nati` varchar(45) NOT NULL,
  `ppno` varchar(45) NOT NULL,
  `cuam` varchar(45) NOT NULL,
  `tcam` varchar(45) NOT NULL,
  `valu` varchar(45) NOT NULL,
  `srch` varchar(45) NOT NULL,
  `stax` varchar(45) NOT NULL,
  `taxp` varchar(45) NOT NULL,
  `roun` varchar(45) NOT NULL,
  `amnt` varchar(45) NOT NULL,
  `encd` varchar(45) NOT NULL,
  `mocd` varchar(45) NOT NULL,
  `endt` varchar(45) NOT NULL,
  `modt` varchar(45) NOT NULL,
  `sflg` varchar(5) NOT NULL,
  `category` varchar(45) NOT NULL DEFAULT 'NA',
  `branch` varchar(10) NOT NULL,
  PRIMARY KEY (`numb`,`branch`,`date`) USING BTREE
) ENGINE=InnoDB DEFAULT CHARSET=latin1 ROW_FORMAT=FIXED

Answer 1

不使用 IN 条件，而是使用 LEFT JOIN NULL

http://explainextended.com/2010/05/27/left-join-is-null-vs-not-in-vs-not-exists-nullable-columns/