CODEWITHSUNDEEP
mysqljoinsubquery
user3253255
user3253255

Reputation: 13

Rewriting MySQL subquery to be a join

I have a table with a lot of rows, and it's very inefficient to do subqueries on. I can't wrap my head around how to do a join on the data to save time.

Here is what I have: http://sqlfiddle.com/#!2/6ab0c/3/0

Upvotes: 0

Views: 42

Answers (2)

Alex
Alex

Reputation: 17289

SELECT I1.*, MIN(I2.exit_date)
FROM Items I1

LEFT JOIN (
  SELECT date as exit_date, location, sku 
  FROM Items
  ORDER BY date asc
) as I2
ON I2.exit_date > I1.date
  AND I2.location = I1.location
  AND I2.sku = I1.sku 
GROUP BY I1.id

Upvotes: 0

Gordon Linoff
Gordon Linoff

Reputation: 1269663

This is a bit long for a comment.

First, I think you are missing an ORDER BY in the subquery. I suspect you want order by I2.date to get the "next" row.

Second, MySQL doesn't quite offer the functionality you need. You could rewrite the query using variables. But, because you don't describe what it is doing, it is hard to be sure that a rewrite would be correct. That is one way to speed the query.

Third, this query would be much faster -- and probably fast enough -- with an index on items(location, sku, date). That index is probably all you need.

Upvotes: 1

Related Questions