以下是SQL, 执行效率很低, 请教该如何优化? 谢谢!
SELECT DISTINCT tpc.categoryuid, tc.name FROM TPRODCATEGORY tpc INNER JOIN TCATEGORY tc ON tpc.categoryuid=tc.uid_pk WHERE tpc.productuid IN (SELECT DISTINCT p.uid_pk FROM TPRODCATEGORY pc INNER JOIN TPRODUCT p ON p.uid_pk=pc.productuid INNER JOIN TPRODUCTVAR pv ON p.uid_pk=pv.productuid WHERE pc.categoryuid=1742)
AND tpc.categoryuid IN (SELECT c.uid_pk FROM TCATEGORY c WHERE c.parentcategory=1712) AND tpc.disabled=0 AND tpc.deleted=0 ORDER BY tc.name
SELECT DISTINCT tpc.categoryuid, tc.name FROM TPRODCATEGORY tpc INNER JOIN TCATEGORY tc ON tpc.categoryuid=tc.uid_pk WHERE tpc.productuid IN (SELECT DISTINCT p.uid_pk FROM TPRODCATEGORY pc INNER JOIN TPRODUCT p ON p.uid_pk=pc.productuid INNER JOIN TPRODUCTVAR pv ON p.uid_pk=pv.productuid WHERE pc.categoryuid=1742)
AND tpc.categoryuid IN (SELECT c.uid_pk FROM TCATEGORY c WHERE c.parentcategory=1712) AND tpc.disabled=0 AND tpc.deleted=0 ORDER BY tc.name
解决方案 »
- Mysql,2个以上词组查询问题
- 求助分组查询
- mysql关于截取字符串并替换的问题
- (求教)用union all 合起来了一个虚拟表,再用select * from 这个表
- 如何获取查询的字段名
- 如何导入*.sql文件???-------------急100分!!!
- MySQL max 4.01 alpha for win32版的到底支不支持事务??
- 我在建立表的时候出现下面问题#1064
- 跪求大侠帮忙解决Invalid query:INSERT INTO g_history6
- 求助!两表查询求和及求减
- 有没有什么办法将mysql datetime类型字段分为 一个date列和一个time列?
- ubuntu下mysql问题
用explain 看一下优化的执行计划。
看看索引有没有。
inner join
(SELECT DISTINCT p.uid_pk FROM TPRODCATEGORY pc INNER JOIN TPRODUCT p ON p.uid_pk=pc.productuid INNER JOIN TPRODUCTVAR pv ON p.uid_pk=pv.productuid WHERE pc.categoryuid=1742) b1
on tpc.productuid=b1.uid_pk
inner join
(SELECT c.uid_pk FROM TCATEGORY c WHERE c.parentcategory=1712) c1
on tpc.categoryuid=c1.uid_pk
where
tpc.disabled=0 AND tpc.deleted=0 ORDER BY tc.name
FROM TPRODCATEGORY tpc INNER JOIN TCATEGORY tc ON tpc.categoryuid=tc.uid_pk
INNER JOIN TPRODUCT p ON p.uid_pk=tpc.productuid
INNER JOIN TPRODUCTVAR pv ON p.uid_pk=pv.productuid
INNER JOIN TCATEGORY c ON tpc.categoryuid=c.uid_pk
WHERE tpc.categoryuid=1742
AND c.parentcategory=1712
AND tpc.disabled=0
AND tpc.deleted=0
ORDER BY tc.name