title: View of products with tags and GROUP_CONCAT
body: <p>I use Sphinx for fulltext search on my app. I use a <code>view</code> to filter the data from a <code>products</code> table and Sphinx indexes this view. Right now I have a hard coded field under the products table with it's related tags, but that is not so good as tags might change and I would have to manipulate the hard coded field every time.</p>
  
  <p>So I was thinking that I could create the view with GROUP_CONCAT, listing all the related tags for me with a syntax like this:</p>
  
  <pre><code>SELECT p.id AS id, GROUP_CONCAT(t.tag SEPARATOR '|') AS tags,
  UNIX_TIMESTAMP(p.created) AS created
  FROM products p
  INNER JOIN products_tags pt ON pt.product_id = p.id
  INNER JOIN tags t ON t.id = pt.tag_id
  AND p.deleted = 0
  AND (p.images IS NOT NULL OR p.images &lt;&gt; '')
  AND p.status LIKE 'listed'
  GROUP BY p.id;
  </code></pre>
  
  <p>The problem with this query is that it takes ages to run. It's really slow. To retrieve only one record, it takes up to 5 seconds. This is the <code>EXPLAIN</code> output:</p>
  
  <pre><code>1, SIMPLE, pt, ALL, , , , , 165029, Using temporary; Using filesort
  1, SIMPLE, p, eq_ref, PRIMARY, PRIMARY, 4, trych_default.pt.product_id, 1, Using where
  1, SIMPLE, t, eq_ref, PRIMARY, PRIMARY, 4, trych_default.pt.tag_id, 1, 
  </code></pre>
  
  <p>I wonder if there is any way to improve the query or maybe a better solution to my problem. Thanks!</p>