MySQL Forums
Forum List  »  Optimizer & Parser

Re: inner join vs left join - huge performance difference
Posted by: michael cook
Date: July 06, 2016 01:51PM

Here's a realistic example.

Source table:
http://pastebin.com/uNJxreER

Basically, think of it as forward looking temperature forecasts. Every hour a new forecast is published for the next 24 hours. So I have two dimensions: 1) the hour for which the forecast value is effective and 2) the hour in which that forecast value was published. I want to be able to do things like select only the 3 hour ahead forecast for each hour, or the forecast for the next hour after. So I have the following structure:

where:
pts = published time stamp
ptshour = calculated column that represents the # of hours after a fixed point in time that this data was published
tshour = calculated column that represents the # of hours after the same fixed point in time that this data is effective for
ts = effective time stamp

the tshour and ptshour columns can be used to get offset, so for example, "WHERE ptshour+3 = tshour" will return only those values that were published 3 hours prior to the effective hour.

Query and correct result:
http://pastebin.com/D8HJJg8Y


Differences between this table and the actual table I'm working with:
1) I have another column called "LocID" that represents one of many different locations
2) I have another column called "type" that represents up to 3 different forecast types
3) a formulaic unique identifier for each row
4) the sample table only has a few values for each hour. the actual data would have many times that (up to 32+ versions per hour)

so... if you multiply all that out, the table is currently just under 28 million rows. Except for this one query that I can't seem to figure out, it seems to be very efficient, and I can select just about anything I'm looking for in miliseconds. It's only this left join that's stumping me

Options: ReplyQuote




Sorry, you can't reply to this topic. It has been closed.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.