Related products approach suggestion, MySQL InnoDB/PHP

Question

I want to expand UI on my CodeIgniter shop with suggestions on what other people bought with the current product (either when viewing product or when product is put in the cart, irrelevant now for the question). I have came up with this query (orders table contains order details, while order items contains products that are in specific order via foreign key, prd alias is for products table where all important info about prduct is stored). Query looks like this

SELECT
  pr.product_id,
  COUNT(*) AS num,
  prd.*
FROM
  orders AS o
  INNER JOIN order_items AS po ON o.id = po.order_id
  INNER JOIN order_items AS pr ON o.id = pr.order_id
  INNER JOIN products AS prd ON pr.product_id = prd.id
WHERE
  po.product_id = '14211'
  AND pr.product_id <> '14211'
GROUP BY
  pr.product_id
ORDER BY
  num DESC
LIMIT 3

It works nice and dandy, query time is 0.030ish seconds and it returns the products that bought together with the one I am currently viewing.

As for the questions and considerations, Percona query analyzer complains about this two things, Non-deterministic GROUP BY and GROUP BY or ORDER BY on different tables, which both I need so that I can get items on top that are actually relevant for the related query, but absolutely have no idea how to fix it, or even should I be really bothered with this notice from query analyzer.

Second question is regarding performace, since for this query, it using temporary and filesort, I was thinking of creating a view out of this query, and use it instead of actually executing the query each time some product is opened.

Mind you that I am not asking for CI model/view/controller tips, just tips on how to optimize this query, and/or suggestions regarding performance and going for views approach...

Any help is much than appreciated.

Answer 1

SELECT  p.num, prd.*
    FROM  
    (
        SELECT  a.product_id, COUNT(*) AS num
            FROM  orders AS o
            INNER JOIN  order_items AS b  ON o.id = b.order_id
            INNER JOIN  order_items AS a  ON o.id = a.order_id
            WHERE  b.product_id = '14211'
              AND  a.product_id <> '14211'
            GROUP BY  a.product_id
            ORDER BY  num DESC
            LIMIT  3 
    ) AS p
    JOIN  products AS prd  ON p.product_id = prd.id
    ORDER BY  p.num DESC

This should

• Run faster (especially as your data grows),
• Avoid the group by complaint,
• not over-inflate the count,
• etc

Ignore the complaint about GROUP BY and ORDER BY coming from different tables -- that is a performance issue; you need it.

As for translating that back to CodeIgniter, good luck.