CODEWITHSUNDEEP
mysqlsql
Tom Harisond
Tom Harisond

Reputation: 57

Optimize query with subquery and join

My query

SELECT a.* FROM hm_all a 
JOIN ( SELECT post_id, MIN(priceUS_USD) as price FROM hm_all GROUP BY post_id ) b 
ON a.post_id = b.post_id AND a.priceUS_USD = b.price 
ORDER BY a.post_id ASC LIMIT 0,20

Explain

id | select_type | table      | type   | possible_keys | key    | key_len | ref  | rows | Extra
1    PRIMARY       a            index    post_id         post_id  4         NULL   1      Using where
1    PRIMARY       <derived2>   ref      key0            key0     9         a.po.  10
2    DERIVED       hm_all       index    NULL            post_id  4         NULL   12112

The query works fine but slow. I wonder how to speed up?

Upvotes: 1

Views: 124

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1269773

I would suggest writing this as a correlated subquery:

select a.*
from hm_all a
where a.priceUS_USD = (select min(a2.priceUS_USD) 
                       from hm_all a2
                       where a2.post_id = a.post_id
                      )
order by a.post_id
limit 20;

Then for performance you want an index on hm_all(post_id, priceUS_USD).

Upvotes: 2

Related Questions