Mysql多对多查询、列合并
表信息
资源表 synsource 199824 rows产品表 tab_product_detail 108 rows资源与产品多对多的关联表 tab_r_sourmach 1,113,866 rows以上表均采用MyISAM引擎.
连接测试
因 为方便用户进行更好的资源的搜索，所以需要将资源数据全部建立索引至Lucene中，希望在Lucene中存储的Document为：
select s1.sourid, s1.sourcename , t.product_name from synsource s1 left join tab_r_sourmach c on s1.sourid=c.sourid left join tab_product_detail t on c.product_id = t.product_id where s1.sourceid=1
select s1.*, GROUP_CONCAT(t.product_name SEPARATOR ' ') as product_name from tab_synsource s1 left join tab_r_sourmach c on s1.sourid=c.sourid left join tab_product_detail t on c.product_id = t.product_id where s1.sourceid=1
select c.souridfrom tab_r_sourmach c left join tab_product_detail t on c.product_id = t.product_id group by c.sourid limit 500;
以上语句用时：(5.65 sec)
再加上GROUP_CONCAT试试：
select c.sourid, GROUP_CONCAT(t.product_name SEPARATOR ' ') as productsfrom tab_r_sourmach c left join tab_product_detail t on c.product_id = t.product_id group by c.sourid limit 800;
