1

I have the following query. It calculates the best match for each row in flight based on the the data in airport

SELECT flight.id,
       (
           SELECT id
           FROM   airport
           WHERE  SQRT(
                      POW(69.1 * (latitude - flight.lat), 2) + POW(69.1 * (flight.lon - longitude) * COS(latitude / 57.3), 2)
                  ) < 10
           ORDER BY
                  ABS(
                      (magnetic -180) -ABS(
                          atan2(TAN(longitude - flight.lon), TAN(latitude -flight.lat)) * (180 / (22 / 7))
                      )
                  ) ASC LIMIT 1
       ) AS airport_id
FROM   flight
WHERE  STATUS = 1

Is there a way to remove the DEPENDENT SUBQUERY and change it to a JOIN? I can even put all the lat/lon in the same table without much effort using UNION.

SELECT id,
       lat,
       lon,
       NULL AS latitude,
       NULL AS longitude,
       NULL AS magnetic
FROM   flight 
    UNION 
SELECT id,
       NULL,
       NULL,
       latitude,
       longitude,
       magnetic
FROM   airport

enter image description here

  • If the underlying issue is poor performance of the query, then it might help to provide an EXPLAIN PLAN for it. – RDFozz Aug 24 '17 at 15:53
  • Sorry for the late anwser. I have added the explain output from the SELECT. – blackswan Aug 30 '17 at 13:22
1

I can't see why it would be useful to perform the UNION you describe (and, assuming flight.id and airport.id are the respect primary keys of those tables, there's no reason not to do a UNION ALL, avoiding checking for duplicates). To be specific, it seems like you'd be dumping the data from the two separate tables into one (in effect) separating them back out into flight and airport in the self-join.

To shift the sub-query into a JOIN, we'd also need a way to select the highest ranked airport returned. We'd wind up with something like this:

  SELECT id
        ,airport_id
  FROM   (
          SELECT flight.id
                ,airport.id as airport_id
                ,@RowNum := CASE WHEN @PrevFlightID = flight.id AND @PrevAirportId = airport.id
                              THEN @RowNum + 1
                              ELSE 1
                            END as rank
                ,@PrevFlightID := flight.id
                ,PrevAirportID := airport.id
          FROM   flight
                   INNER JOIN airport ON SQRT(
                                                POW(69.1 * (airport.latitude - flight.lat), 2)
                                              + POW(69.1 * (flight.lon - airport.longitude) * COS(airport.latitude / 57.3), 2)
                                             ) < 10
                   CROSS JOIN (SELECT @RowNum := 0
                                     ,@PrevFlightID := 0
                                     ,@PrevAirportID := 0
                              ) v
          WHERE  flight.STATUS = 1
          ORDER BY
                 ABS(
                      (airport.magnetic -180)
                     -ABS(atan2(TAN(airport.longitude - flight.lon), TAN(airport.latitude -flight.lat)) * (180 / (22 / 7)))
                    )
         ) ranked_data
  WHERE rank = 1
  ;

which, of course, still has a subquery, to filter out the rows we don't want. If I were you, I'd run some tests with both options, and see if one performs notably better than the other. If not, I suspect I'd use what you already have; in my opinion, it's a little easier to follow.

(Note: code untested)

FYI: given that you have to plug the values from the tables into a somewhat complex equation in order to match them up, it's unlikely that adding indexes or anything like that would help in the matching process. If either table is relatively wide (has many more columns than the ones needed in the query), you might get better performance by creating covering indexes (indexes that have the columns this query needs; the engine can use that instead of the table, and probably will if the index has notably more rows per page than the table itself).

If this information is required frequently, but the flight and airport tables are not updated constantly during the day, then you might save the results of the query to a table. This would work best if the flight table is updated nightly (I assume the airport table is basically static), for example. Note that if the flight table is updated off the normal schedule, the saved results would need to be recomputed.

  • i thought that if I put them in the same table that it would be possible to do a self join to it to get similar results using group by. – blackswan Aug 23 '17 at 18:27
  • It's just because when I add an update to my query it seems awfully slow. The SELECT execution time is 0.6s which would be ok for me for backend. I guess that I have to add more filters to it. If anyone has a glorious idea, anytime! Thanks – blackswan Aug 24 '17 at 14:03

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.