select INFO_ID,Title,MODIFY_DATE from TB_TEXT where INFO_ID in (select INFO_ID from TB_TEXT_LINK where COLUMN_ID like '01%') and STATUS='1' order by MODIFY_DATE desc select A.INFO_ID,A.Title,A.MODIFY_DATE from TB_TEXT A,TB_TEXT_LINK B where A.INFO_ID=B.INFO_ID and B.COLUMN_ID like '01%' and A.STATUS='1' order by MODIFY_DATE descB.COLUMN_ID和A.INFO_IDA.加索引
去掉ORDER BY ,时间如何?
谢谢,我换成了你的语句,速度果然快了!看来select in 本身就慢,很难优化了
mysql5.6可以用 in (select xxx)语句 其余版本禁用
就像 rucypli 所说的那句sql可以么。用join之类的办法。 反正能不用in就不要用in。
贴出你的 explain select ... show index from ... 以供分析。
rucypli正解,另外你的索引建重复了。 PRIMARY KEY (`info_id`), UNIQUE KEY `index_id` (`info_id`) USING BTREE
我刚接触mysql, explain select .是什么?
我刚接触mysql, explain select .是什么? explain select INFO_ID,Title,MODIFY_DATE from TB_TEXT where INFO_ID in (select INFO_ID from TB_TEXT_LINK where COLUMN_ID like '01%') and STATUS='1' order by MODIFY_DATE desc就是sql的执行计划,你贴出来大家相对好分析一点。
我试了在mysql里可以运行的语句 放到hibernate里面报了这个错误,不知道什么原因,对HQL也不熟啊,: Invalid path: 'null.infoId' 2014-10-14 23:20:43,533 ERROR [http-8080-1] (ErrorCounter.java:56) - <AST>:0:0: unexpected end of subtree 2014-10-14 23:20:43,533 ERROR [http-8080-1] (ErrorCounter.java:56) - left-hand operand of a binary operator was null 2014-10-14 23:20:43,534 ERROR [http-8080-1] (ErrorCounter.java:56) - <AST>:0:0: unexpected end of subtree 2014-10-14 23:20:43,540 ERROR [http-8080-1] (ErrorCounter.java:56) - Invalid path: 'null.status' 2014-10-14 23:20:43,541 ERROR [http-8080-1] (ErrorCounter.java:56) - <AST>:0:0: unexpected end of subtree 2014-10-14 23:20:43,541 ERROR [http-8080-1] (ErrorCounter.java:56) - left-hand operand of a binary operator was null org.springframework.orm.hibernate3.HibernateQueryException: Invalid path: 'null.infoId' [ select count (*) from com.baobaotao.domain.NewsInfo,com.baobaotao.domain.TextLink where NewsInfo.infoId=TextLink.infoId and TextLink.columnId like '01%' and NewsInfo.status=1 ]; nested exception is org.hibernate.hql.ast.QuerySyntaxException: Invalid path: 'null.infoId' [ select count (*) from com.baobaotao.domain.NewsInfo,com.baobaotao.domain.TextLink where NewsInfo.infoId=TextLink.infoId and TextLink.columnId like '01%' and NewsInfo.status=1 ]
现在让我比较困惑的是我看人家跟我一样结构的表,用的Select in 速度很快,但就是Oracle库,我就不明白是怎么建的索引了
select A.INFO_ID,A.Title,A.MODIFY_DATE
from TB_TEXT A,TB_TEXT_LINK B
where A.INFO_ID=B.INFO_ID and B.COLUMN_ID like '01%' and A.STATUS='1'
order by MODIFY_DATE descB.COLUMN_ID和A.INFO_IDA.加索引
谢谢,我换成了你的语句,速度果然快了!看来select in 本身就慢,很难优化了
反正能不用in就不要用in。
show index from ...
以供分析。
PRIMARY KEY (`info_id`),
UNIQUE KEY `index_id` (`info_id`) USING BTREE
explain select INFO_ID,Title,MODIFY_DATE from TB_TEXT where INFO_ID in (select INFO_ID from TB_TEXT_LINK where COLUMN_ID like '01%') and STATUS='1' order by MODIFY_DATE desc就是sql的执行计划,你贴出来大家相对好分析一点。
Invalid path: 'null.infoId'
2014-10-14 23:20:43,533 ERROR [http-8080-1] (ErrorCounter.java:56) - <AST>:0:0: unexpected end of subtree
2014-10-14 23:20:43,533 ERROR [http-8080-1] (ErrorCounter.java:56) - left-hand operand of a binary operator was null
2014-10-14 23:20:43,534 ERROR [http-8080-1] (ErrorCounter.java:56) - <AST>:0:0: unexpected end of subtree
2014-10-14 23:20:43,540 ERROR [http-8080-1] (ErrorCounter.java:56) - Invalid path: 'null.status'
2014-10-14 23:20:43,541 ERROR [http-8080-1] (ErrorCounter.java:56) - <AST>:0:0: unexpected end of subtree
2014-10-14 23:20:43,541 ERROR [http-8080-1] (ErrorCounter.java:56) - left-hand operand of a binary operator was null
org.springframework.orm.hibernate3.HibernateQueryException: Invalid path: 'null.infoId' [ select count (*) from com.baobaotao.domain.NewsInfo,com.baobaotao.domain.TextLink where NewsInfo.infoId=TextLink.infoId and TextLink.columnId like '01%' and NewsInfo.status=1 ]; nested exception is org.hibernate.hql.ast.QuerySyntaxException: Invalid path: 'null.infoId' [ select count (*) from com.baobaotao.domain.NewsInfo,com.baobaotao.domain.TextLink where NewsInfo.infoId=TextLink.infoId and TextLink.columnId like '01%' and NewsInfo.status=1 ]