select a.cat_name,a.id,a.cat_sort,sum(c.cont) as cont,sum(d.cont) as date
from category as a
left join category as b on a.id=b.pid
left join (select typeno,count(id) as cont from products where lang = 'en' group by typeno ) c on b.cat_no=c.typeno
left join (select typeno,count(id) as cont from products where lang = 'en' and pubdate =
now() group by typeno ) d on b.cat_no=d.typeno
where a.pid=0 and a.lang='en'
GROUP BY a.cat_name
from category as a
left join category as b on a.id=b.pid
left join (select typeno,count(id) as cont from products where lang = 'en' group by typeno ) c on b.cat_no=c.typeno
left join (select typeno,count(id) as cont from products where lang = 'en' and pubdate =
now() group by typeno ) d on b.cat_no=d.typeno
where a.pid=0 and a.lang='en'
GROUP BY a.cat_name
explain select a.cat_name,a.id,a.cat_sort,sum(c.cont) as cont,sum(d.cont) as date
from category as a
left join category as b on a.id=b.pid
left join (select typeno,count(id) as cont from products where lang = 'en' group by typeno ) c on b.cat_no=c.typeno
left join (select typeno,count(id) as cont from products where lang = 'en' and pubdate =
now() group by typeno ) d on b.cat_no=d.typeno
where a.pid=0 and a.lang='en'
GROUP BY a.cat_name
;
show index from table1;
show index from table2;
..所以上面SQL语句用到的表。