Mysql – Is it more performant to use a JOIN or a when using LIMIT 1

MySQL

I have a products table. I want to select all fields (of a given row) and the most profitable discount that the user can claim (depends on ordered quantity of the product).

When selecting only one value only, is it more performant to use a JOIN, like this:

SELECT p.*, qd.price AS qd_price

FROM products AS p

-- Find all applicable QDs
LEFT OUTER JOIN quantity_discounts as qd
    ON p.id=qd.product_id
    AND qd.min_amount <= ?
    AND (
        (qd.user_id IS NULL OR qd.user_id = 0 OR qd.user_id = ?)
        OR (qd.group_id IS NULL OR qd.group_id = 0 OR qd.group_id = ?)
    )

WHERE p.id=?

-- Get the lowest QD price
ORDER BY qd.price ASC
LIMIT 1

or a subquery, like this:

SELECT p.*,
(SELECT price FROM quantity_discounts
WHERE product_id=p.id
AND (
    (user_id=? or user_id=0 or user_id IS NULL)
    OR (group_id=? or group_id=0 or group_id IS NULL)
)
AND qd.min_amount <= ?
ORDER BY price LIMIT 1) AS qd_price
FROM products as p
WHERE p.id=?

Best Answer

The ORs are deadly for performance. For starters, decide on 0 or NULL so you won't have to test for both. (That won't actually help any since there would still be other ORs.)

I'm dubious about the formulation of the LEFT JOIN. (BTW, OUTER is optional.) Normally when using LEFT, the ON only says how the tables are related (p.id=qd.product_id); the WHERE provides the filtering.

I don't know which is better, but since you seem to be limiting to one id (via WHERE p.id=?), then the second formulation will look into quantity_discounts only once, and (hopefully) won't have too many rows to check. That is, the OR may not be as bad as I started out with.

A slight help on quantity_discounts will be

INDEX(product_id, price)  -- in this order

Now I am worried. One formulation has AND qd.min_amount <= ?, the other does not.

Related Solutions

Mysql – Slow performance of MySQL Join Query

Please provide SHOW CREATE TABLE; the explain is useless without it.

OR is a performance killer in many contexts.

( p.pricelist = "name_abc" AND p.iln = "sellerID_123" ) OR ( p.pricelist = "name_def" AND p.iln = "sellerID_456" ) OR ...

Turn that into

JOIN ( SELECT id FROM p WHERE 
( p.pricelist = "name_abc" AND p.iln = "sellerID_123" ) OR 
( p.pricelist = "name_def" AND p.iln = "sellerID_456" ) OR ... ) x ON x.id = foo.id

Also needed (on p):

INDEX(pricelist, iln, id)

(With the CREATEs, I could be more specific.)

The idea behind this "trick" is to move the costly work of the OR into a subquery that returns the necessary ids. Plus the INDEX makes it so that it can do all that work in the INDEX.

Mysql – Selecting minimum value using a subquery

Modifying slightly your second query, will give you both the merchant id and the lowest price (over all products that pass the conditions - I guess that's what you want):

SELECT p.p_m_id, MIN(p_price) AS min_p_price 
FROM tgmp_affiliates ga 
JOIN tgmp_prices p 
    ON ga.a_code = p.p_gtin 
        AND ga.a_code > '' 
JOIN tgmp_merchants m 
    ON m.m_id = p.p_m_id 
WHERE ga.site_id = '34' 
    AND p.site_id = '34' 
    AND ga.a_parent = '25573' 
    AND p.p_type = 'games' 
    AND m.m_hide = 0 
GROUP BY p.p_m_id ;

Then you can join this - as a derived table - to all the tables that you need data from in the results:

SELECT
    m.*, p.*, ga.*                     -- whatever columns you want  
FROM tgmp_affiliates ga 
JOIN tgmp_prices p 
    ON ga.a_code = p.p_gtin 
        AND ga.a_code > '' 
JOIN tgmp_merchants m 
    ON m.m_id = p.p_m_id 
JOIN
      ( SELECT p.p_m_id, MIN(p_price) AS p_price 
        FROM tgmp_affiliates ga 
        JOIN tgmp_prices p 
            ON ga.a_code = p.p_gtin 
                AND ga.a_code > '' 
        JOIN tgmp_merchants m 
            ON m.m_id = p.p_m_id 
        WHERE ga.site_id = '34' 
            AND p.site_id = '34' 
            AND ga.a_parent = '25573' 
            AND p.p_type = 'games' 
            AND m.m_hide = 0 
        GROUP BY p.p_m_id 
      ) AS tmp
    ON  tmp.p_m_id = p.p_m_id 
    AND tmp.p_price = p.p_price
WHERE ga.site_id = '34' 
    AND p.site_id = '34' 
    AND ga.a_parent = '25573' 
    AND p.p_type = 'games' 
ORDER BY p.p_price ;

Best Answer

Related Solutions

Mysql – Slow performance of MySQL Join Query

Mysql – Selecting minimum value using a subquery

Related Question