在Mysql中用到多表关联,内容表的每条记录分别对应资源表中的图片和视频两条记录,要求查询每个内容的信息,包括图片和视频。
第一个版本:循环每条内容,查询对应的图片和视频,组合成一条信息,费时>10分钟
第二个版本:一条SQL搞定,使用嵌套查询,费时2~3分钟
select
a.INDEXID,c.TITLE,c.CREATEDTIME,c.INTRO,d.picurl,e.src,e.size,e.info
from mms_content_index a,mms_index_node b,mms_content_table_10 c,(
select a.src as picurl,b.indexid from mms_resource a,mms_resource_ref b
where a.resourceid = b.resourceid and a.resourcetype = ' img ' ) d ,
( select a.src,a.size,a.info,b.indexid from mms_resource a,mms_resource_ref b
where a.resourceid = b.resourceid and a.resourcetype <> ' img ' ) e where a.INDEXID = b.INDEXID
and b.STORAGENODEID = 354 and a.CONTENTID = c.CONTENTID and a.FLAG = 1 and d.indexid = a.indexid
and e.indexid = a.indexid
order by c.CREATEDTIME desc ;
from mms_content_index a,mms_index_node b,mms_content_table_10 c,(
select a.src as picurl,b.indexid from mms_resource a,mms_resource_ref b
where a.resourceid = b.resourceid and a.resourcetype = ' img ' ) d ,
( select a.src,a.size,a.info,b.indexid from mms_resource a,mms_resource_ref b
where a.resourceid = b.resourceid and a.resourcetype <> ' img ' ) e where a.INDEXID = b.INDEXID
and b.STORAGENODEID = 354 and a.CONTENTID = c.CONTENTID and a.FLAG = 1 and d.indexid = a.indexid
and e.indexid = a.indexid
order by c.CREATEDTIME desc ;
第三个版本:一条SQL,多表关联,费时<10秒
s
elect a.INDEXID,c.TITLE,c.CREATEDTIME,c.INTRO,d.src
as
picurl,e.src,e.size,e.info
from mms_content_index a,mms_index_node b,mms_content_table_10 c, mms_resource d,
mms_resource e,mms_resource_ref f ,mms_resource_ref g where a.INDEXID = b.INDEXID
and b.STORAGENODEID = 354 and a.CONTENTID = c.CONTENTID and a.FLAG = 1 and d.resourceid = f.resourceid
and e.resourceid = g.resourceid and d.resourcetype = ' img ' and e.resourcetype <> ' img '
and f.indexid = a.indexid and g.indexid = a.indexid
order by c.CREATEDTIME desc ;
from mms_content_index a,mms_index_node b,mms_content_table_10 c, mms_resource d,
mms_resource e,mms_resource_ref f ,mms_resource_ref g where a.INDEXID = b.INDEXID
and b.STORAGENODEID = 354 and a.CONTENTID = c.CONTENTID and a.FLAG = 1 and d.resourceid = f.resourceid
and e.resourceid = g.resourceid and d.resourcetype = ' img ' and e.resourcetype <> ' img '
and f.indexid = a.indexid and g.indexid = a.indexid
order by c.CREATEDTIME desc ;
第四个版本:还没搞