MySQL has difficulty optimising subqueries, when you write something like:
SELECT *
FROM T
WHERE T.ID (SELECT ID FROM T2);
It is sometimes rewritten as
SELECT *
FROM T
WHERE EXISTS
( SELECT 1
FROM T2
WHERE T.ID = T2.ID
);
The subquery is then executed once per row in T
, whereas if you write:
SELECT T.*
FROM T
INNER JOIN
( SELECT DISTINCT ID
FROM T2
) T2
ON T2.ID = T.ID;
Your result set will be the same, but MySQL will first fill an in memory table with the results of the subquery and hash it on T2.ID, it then just needs to lookup against this hash table for each row in T
.
Which behaviour you want really depends on how much data you are expecting from each table/subquery. If you have 1 million rows in T2
, and 10 in T
then there is no point in filling a temporary table with 1 million rows, only to subsequently only use it 10 times, whereas if you have a large number of rows in T
and only a small amount in T2
the additional cost of materialising the subquery will be beneficial in the long run.
Another thing to point out (which has no impact on performance), the JOIN syntax you are using is the ANSI 89 syntax and was replaced by ANSI 92 explicit JOIN syntax over 20 years ago. Although directed at SQL Server, I think this article summarises the reasons to switch to the newer join syntax very well. Making your final query:
SELECT *
FROM product p,
INNER JOIN store s
ON p.store = s.idstore
INNER JOIN branches b
ON b.idproduct = p.idproduct
INNER JOIN
( SELECT DISTINCT p.common_name
FROM shopping_list_content s
INNER JOIN product p
ON s.iditem = p.idproduct
WHERE s.idlist =$listid
) s
ON s.common_name = p.common_name;
N.B. Most of the above does not apply if you are using MySQL 5.6.5 or later. In this version they introduced more Subquery Optimization that solved a lot of the above issues