List:General Discussion« Previous MessageNext Message »
From:SGreen Date:April 15 2005 6:34pm
Subject:Re: LEFT JOINS same data twice?
View as plain text  
"Chris Knipe" <savage@stripped> wrote on 04/15/2005 01:20:33 PM:

> > I eliminated your GROUP BY clause because you weren't actually 
GROUPing
> > anything. You weren't looking for a MIN(), a MAX(), an AVG(), or 
anything
> > else that GROUP BY was intended to be used for. That method of
> > de-duplicating results will return potentially misleading information 
as
> > it will randomly pick just one record from all of the records that fit
> > within that "group". This way, by using DISTINCT, you can see if 
things
> > change mid-flight or not (which is either an indication of bad data or
> > some other troubles).
> 
> Ok, again I should have explained this.  The reason why FlightData is so 

> huge and growing so rapidly, is because we take a snapshot every minute 
of 
> every single flight we monitor.  I therefore group by a time stamp 
and/or 
> FlightTracker (Depending what data I need), so that I can only get the 
data 
> returned each time the data (plane's position) actually changed.  A 
quick 
> example:

I remember you mentioning that in an earlier post. Sorry I didn't remember 
it for my reply. So you only want to join to the most recent FlightData 
record for any flight. Subqueries are one way to go but you can't use them 
if your MySQL is older than 4.1. (Personally, I don't use them if I can 
avoid them.)

This is the "groupwise maximum" problem that is a FAQ and is documented in 
the manual. (However, I think you already read those)

> 
> mysql> SELECT * FROM FlightData LIMIT 2\G
> *************************** 1. row ***************************
>       EntryID: 3471
>       Network: V
>     TimeStamp: 2005-04-14 07:27:42
>       Tracker: df0363ea-fe06-1027-b1dd-00e01884a424
> PilotCallSign: xxx
>      PilotCID: xxx
> PilotRealName: Abdul Henderson KDFW
>    CurHeading: 124
>        CurAlt: 34887
>         CurGS: 469
>         Plane: H/DC10/W
>   Transponder: 7207
>         QNHHg: 29.80
>         QNHMb: 1009.14
>       Enroute: 06:00:00
>          Feul: 10:00:00
>    AirportDep: KLAX
>    AirportDes: MMMX
>    AirportAlt:
>   PositionCur: 32.06073/-114.70269
>   PositionDep: 33.942536/-118.408075
>   PositionDes: 19.435278/-099.072222
>          PLGS: 480
>         PLAlt: 35000
>     PLDepTime: 640
>  PLFlightType: I
> *************************** 2. row ***************************
>       EntryID: 3472
>       Network: V
>     TimeStamp: 2005-04-14 07:37:42
>       Tracker: df0363ea-fe06-1027-b1dd-00e01884a424
> PilotCallSign: xxx
>      PilotCID: xxx
> PilotRealName: xxx
>    CurHeading: 255
>        CurAlt: 35707
>         CurGS: 470
>         Plane: B738
>   Transponder: 7207
>         QNHHg: 29.96
>         QNHMb: 1014.56
>       Enroute: 02:12:00
>          Feul: 05:30:00
>    AirportDep: KPDX
>    AirportDes: KLAX
>    AirportAlt:
>   PositionCur: 33.94045/-118.40567
>   PositionDep: 45.588722/-122.597500
>   PositionDes: 33.942536/-118.408075
>          PLGS: 350
>         PLAlt: 34000
>     PLDepTime: 220
>  PLFlightType: I
> 2 rows in set (0.00 sec)
> 
> FlightTracker therefore appears in the database a couple of hundred 
times 
> for each flight.  It is inserted each and every time we poll the flight, 

> whether the data has changed or not.  If I do not group therefore, my 
> original query would return the Deparutre / Arrival for each and every 
> occurance for FlightTracker.  I do however understand what you are 
saying, 
> and I was not aware that it would Group the GROUP / JOIN the entire 
table 
> before adding the exceptions.  This is obviously my problem as well.
> 
> Mainly, I use the TimeStamp to determine WHEN last the position of a 
flight 
> was changed (and thus I know when and what change was made in the 
flight), 
> whilst FlightTracker is used to "group" all the occurances of the 
changes in 
> the flight.
> 
>  > This command would fit between the CREATE...SELECT... and the second
> > SELECT... Even though the docs say that only 1 index is ever used per
> > query, I believe that each JOIN can also make use of an index if it 
exists
> > (this belief is based on the results of the EXPLAIN(s) of queries
> > containing JOINed tables. However, I could be completely wrong in this
> > belief.).
> >
> 
> I was seriously hoping to be able to do this effeciently with one query. 

> But what you are saying does make sense however - especially when the 
> database gets bigger and we're sitting with 1GB of flight data for 
example. 
> One question though, if we sit with (for example) with 1,000,000 records 

> instead of the odd 350K records in the database now.  Will it not take 
just 
> as long to populate the temp table?

I think you may need you to reconsider your current storage design. You 
may want two FlightData tables, one to hold the most recent entry 
(FlightDataCurrent) the other to store all of the records of a flight 
(FlightDataHistory). They can have the same table structure but they exist 
for two totally different purposes.

Run your "current status" queries against the "current" table while if you 
want in-depth history of a particular flight, use the "history" table. 
This will increase your responsiveness considerably as there should be 
only 1 record per active flight in that table.

There are several ways to approach this: 

a) INSERT everything into the history table then on a periodic basis, 
regenerate the Current table.
b) INSERT records into both tables and DELETE the old one from the history 
table.
c) INSERT records into both tables and flag the old ones in the current 
table as expired. Delete the "expired" records on a periodic basis.

Have you thought of using InnoDB. I think it would be a good fit for this 
as you would definitely be using transactions and you need to read and 
write frequently against the same table and I don't think the MyISAM model 
of locking the whole table will meet your concurrency needs.

> 
> > I do not think this technique is faster if you eliminate the WHERE 
clause
> > from the first query. But if you do have a WHERE, it should return 
much
> > faster than if you simply added a WHERE to your original query.
> 
> Ok.  That is something that I was not sure about either yes.  WHERE 
would 
> obviously make the JOINs / GROUP BY smaller in regards to the amount of 
data 
> examined and processed.  I thought perhaps LIMIT would do the same, in 
that 
> it would use only the 30 records specified in my example query. 
Obviously 
> it JOIN / GROUP the entire table first, and only then return the first 
30 
> records - which is indeed a big difference.
> 
> mysql> SELECT FlightData.PilotCallSign AS FLNum, FlightLogs.Tracker 
> AS Tracker,         FlightLogs.TimeStart AS TimeDep, Departure.IATA 
> AS AirpDep,         Arrival.IATA AS AirpDes,         FlightLogs.Plane AS 

> Plane    FROM FlightLogs    LEFT JOIN Airports AS Departure ON 
> FlightLogs.AirpDep=Departure.ICAO    LEFT JOIN Airports AS Arrival ON 
> FlightLogs.AirpDes=Arrival.ICAO    LEFT JOIN FlightData ON 
> FlightLogs.Tracker=FlightData.Tracker   GROUP BY FlightData.Tracker 
ORDER 
> BY FlightLogs.TimeStart   LIMIT 30;
> +---------+--------------------------------------
> +---------------------+---------+---------+----------+
> | FLNum   | Tracker                              | TimeDep             | 

> AirpDep | AirpDes | Plane    |
> +---------+--------------------------------------
> +---------------------+---------+---------+----------+
> <SNIP>
> +---------+--------------------------------------
> +---------------------+---------+---------+----------+
> 30 rows in set (14.43 sec)
> 
> mysql> SELECT FlightData.PilotCallSign AS FLNum, FlightLogs.Tracker 
> AS Tracker,         FlightLogs.TimeStart AS TimeDep, Departure.IATA 
> AS AirpDep,         Arrival.IATA AS AirpDes,         FlightLogs.Plane AS 

> Plane    FROM FlightLogs    LEFT JOIN Airports AS Departure ON 
> FlightLogs.AirpDep=Departure.ICAO    LEFT JOIN Airports AS Arrival ON 
> FlightLogs.AirpDes=Arrival.ICAO    LEFT JOIN FlightData ON 
> FlightLogs.Tracker=FlightData.Tracker WHERE FlightLogs.CCID='927968' 
GROUP 
> BY FlightData.Tracker   ORDER BY FlightLogs.TimeStart;
> +--------+--------------------------------------
> +---------------------+---------+---------+--------+
> | FLNum  | Tracker                              | TimeDep             | 
> AirpDep | AirpDes | Plane  |
> +--------+--------------------------------------
> +---------------------+---------+---------+--------+
> <SNIP>
> +--------+--------------------------------------
> +---------------------+---------+---------+--------+
> 2 rows in set (0.02 sec)
> 
> MAJOR difference indeed ;)  I'll check out the temp tables a bit later 
after 
> I had some sleep.  I think I'll need it for what I'm going to be 
exploring 
> here... hehehe  One last question... A sub select may not perhaps speed 
> things up as well?
> 
> --
> Chris.
> 

Getting any data out of a billion row table (1 million rows of FlightData 
x rows of Flights x Airports x Airports) is always going to take some 
time. If you can limit what your most frequently run queries need to query 
against, you will improve your performance considerably. OLTP and OLAP 
databases have almost mutually exclusive designs. The long-term history of 
a flight resembles an OLAP problem while the "what's going on right now" 
query is more of an OLTP problem. I honestly think that splitting your 
FlightData table will be a better design in the long run. Yes, it means 
more work when you INSERT a new record but I believe you will be saving 
more time on your queries than you will be losing through the extra 
effort. 

Shawn Green
Database Administrator
Unimin Corporation - Spruce Pine





Thread
LEFT JOINS same data twice?Chris Knipe13 Apr
  • Re: LEFT JOINS same data twice?Peter Brawley13 Apr
  • Re: LEFT JOINS same data twice?Rhino13 Apr
  • Re: LEFT JOINS same data twice?Roger Baklund13 Apr
  • Re: LEFT JOINS same data twice?Chris Knipe15 Apr
    • Re: LEFT JOINS same data twice?SGreen15 Apr
      • Re: LEFT JOINS same data twice?Peter Brawley15 Apr
      • Re: LEFT JOINS same data twice?Chris Knipe15 Apr
        • Re: LEFT JOINS same data twice?SGreen15 Apr
          • Re: LEFT JOINS same data twice?SGreen15 Apr