各位大神,我数据库里有一张菜单表,有id,pid和title,想要一次性查询所有父菜单下面所有的子子孙孙菜单的个数,在网上查了很久,也能实现,但查询时间太长,10万条数据以下超1分钟,不能接受。请哪位大神帮忙优化一下,感谢!
SELECT cid AS id, pid, code AS title, count(cid) AS totle FROM (SELECT * FROM (SELECT url AS path, id, cid, pid, CODE, grade FROM menu WHERE isphoto=0) AS a LEFT JOIN (SELECT url, pid AS parent FROM menu WHERE isphoto=1) AS b ON LOCATE(a.path, b.url)) AS c GROUP BY cid
实在没有积分,请大神帮忙一下。