CODEWITHSUNDEEP
mysql
Epicurus
Epicurus

Reputation: 2133

Slow MySQL left join query

I have the following 3 MySQL tables (shown as table_name:column_list):

tbl_product: id, name
tbl_sales: productid, date
tbl_impressions: productid, date

tbl_products stores available products, tbl_sales stores each sale of a product and tbl_impressions stores each impression of a product. I would like to find products that have been sold, displayed, or both. My attempted query for this is:

SELECT prod.name
FROM tbl_product prod
LEFT JOIN tbl_sales sales ON sales.productid=prod.id
LEFT JOIN tbl_impressions imp ON imp.productid=prod.id
WHERE (sales.productid IS NOT NULL) OR (imp.productid IS NOT NULL)

However, in my tests this query is extremely slow. What options do I have for improving it?

Upvotes: 1

Views: 415

Answers (3)

Peter Lang
Peter Lang

Reputation: 55514

Using EXISTS instead of your LEFT JOINs should help.

SELECT prod.name
FROM tbl_product prod
WHERE EXISTS ( SELECT 1 FROM tbl_sales sales WHERE sales.productid = prod.id )
   OR EXISTS ( SELECT 1 FROM tbl_impressions imp WHERE imp.productid = prod.id )

Make sure to add indexes on tbl_sales.productid and tbl_impressions.productid. (as others said)

Upvotes: 1

Sebas
Sebas

Reputation: 21522

SELECT name FROM tbl_product INNER JOIN tbl_sales ON id = productid
UNION
SELECT name FROM tbl_product INNER JOIN tbl_impressions ON id = productid

Upvotes: 2

Romil Kumar Jain
Romil Kumar Jain

Reputation: 20745

Use Index on productid and id fields in all these table. The JOIN will give performance if we have index on joining fields.

Upvotes: 4

Related Questions