title: MySQL group_concat + subquery or substring_index
body: <p>Well i got 2 tables:</p>
  
  <pre><code>- tb_shops
  shop_id PK
  shop_name
  
  - tb_products
  
  product_id PL
  product_name
  shop_id FK
  </code></pre>
  
  <p>I need to list something like:</p>
  
  <pre><code>shop_name | product01,product02,product03,product04
  shop_name2 | product05,product06,product07,prodcut08
  </code></pre>
  
  <p>but these products need to have a limit 4 per shop.</p>
  
  <p>Well i tried these queries:</p>
  
  <p>Fist with subquery</p>
  
  <pre><code>select SQL_NO_CACHE a.shop_id,
          a.shop_name,
          count(b.product_id) as ptotal,
          (select group_concat(_b.product_name separator ',') from tb_shops _a left join tb_products _b using(shop_id) where _a.shop_id=a.shop_id group by _a.shop_id limit 4) as products 
  from tb_shops a 
  left join tb_products b on a.shop_id=b.shop_id 
  group by a.shop_id
  order by a.shop_name asc
  </code></pre>
  
  <p>Second with substring_index</p>
  
  <pre><code>select SQL_NO_CACHE a.shop_id,
          a.shop_name,
          count(distinct b.product_id) as ptotal,
          substring_index(group_concat(distinct c.product_name separator ','),',',4) as products 
  from tb_shops a 
  left join tb_products b on a.shop_id=b.shop_id 
  left join tb_products c on a.shop_id=c.shop_id 
  group by a.shop_id
  order by a.shop_name asc
  </code></pre>
  
  <p>which these is better? or someone knows a better solution?</p>