我用的是mysql5.0,我程序中用到了一句sql是select count(DISTINCT id) from Policyinfo p where p.filename not in
(select p2.filename from Policyreadinfo p2 where p2.reader='test')
但是每次在程序中每次执行这句sql时网页就特慢,要10多秒,而这2个表的记录行数也就1000多而已,我把这句sql单独拿到SQLyog Enterprise里执行也很慢,和网页的执行时间差不多。我想在policyreadinfo表里建个索引,但是每次我执行建立索引的sql时,MYSQL就会停掉,建不了索引。我用的到的结果如下:
"id", "select_type", "table","type","possible_keys","key","key_len","ref","rows","Extra"
"1", "PRIMARY", "p", "ALL", \N, \N, \N, \N, "1715","Using where"
"2", "DEPENDENT SUBQUERY","p2", "ALL", \N, \N, \N, \N, "4196","Using where"
其中表Policyinfo结构为CREATE TABLE `policyinfo` (
`id` int(11) NOT NULL auto_increment,
`filenum` varchar(50) default NULL,
`filename` varchar(100) default NULL,
`filecontent` longtext,
`writetime` datetime default NULL,
`writer` varchar(100) default NULL
PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
Policyreadinfo 表的结构是CREATE TABLE `policyreadinfo` (
`id` int(11) NOT NULL auto_increment,
`filename` varchar(500) default NULL,
`reader` varchar(100) default NULL,
`readtime` datetime default NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
(select p2.filename from Policyreadinfo p2 where p2.reader='test')
但是每次在程序中每次执行这句sql时网页就特慢,要10多秒,而这2个表的记录行数也就1000多而已,我把这句sql单独拿到SQLyog Enterprise里执行也很慢,和网页的执行时间差不多。我想在policyreadinfo表里建个索引,但是每次我执行建立索引的sql时,MYSQL就会停掉,建不了索引。我用的到的结果如下:
"id", "select_type", "table","type","possible_keys","key","key_len","ref","rows","Extra"
"1", "PRIMARY", "p", "ALL", \N, \N, \N, \N, "1715","Using where"
"2", "DEPENDENT SUBQUERY","p2", "ALL", \N, \N, \N, \N, "4196","Using where"
其中表Policyinfo结构为CREATE TABLE `policyinfo` (
`id` int(11) NOT NULL auto_increment,
`filenum` varchar(50) default NULL,
`filename` varchar(100) default NULL,
`filecontent` longtext,
`writetime` datetime default NULL,
`writer` varchar(100) default NULL
PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
Policyreadinfo 表的结构是CREATE TABLE `policyreadinfo` (
`id` int(11) NOT NULL auto_increment,
`filename` varchar(500) default NULL,
`reader` varchar(100) default NULL,
`readtime` datetime default NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
我这个是判断用户有没有阅读过该条记录,是在用户一登陆系统时,给用户作提醒,你还有多少条记录没有阅读而已。页面上也只是把这个查询结果显示出来。修改为join?怎么修改,最好可以给出SQL。谢谢
select count(DISTINCT p.id)
from Policyinfo p left join Policyreadinfo p2 on p.filename=p2.filename
where p2.filename is null
or p2.reader!='test'
(select 1 from Policyreadinfo p2 where p2.reader='test' and p2.filename = p.filename )
`fileid` int(11) NOT NULL,
`readerid` int(11) NOT NULL,
`readtime` datetime default NULL,
PRIMARY KEY (`id`),
key fileid ('fileid'),
key readerid ('readerid')
) ENGINE=InnoDB DEFAULT CHARSET=utf8;这就是一百多万数据的例子:http://www.dfwgw.com/dGFva2UvaG90XzcuaHRtbA==url.html