CODEWITHSUNDEEP
mysqlsqljoinsubquery
ThatBrianDude
ThatBrianDude

Reputation: 3190

Can I avoid my subquery?

My structure is fairly simple: I have ways that consist of multiple points. Each point has a x and y position. Ways are defined with the help of an extra table wayspoints which contains the columns wid and pid.

What I have to do is fetch all ways (with all of there points) within a certain distance to a given x and y.

I do this by checking the difference between the points x,y and the given x,y.

The problem I have is that I dont want just all points within the distance but rather all points that belong to each way that has points within the distance.

Before I had this requirement my query ran for about 100ms fetching 1 million points. Since I now need each way in its whole I added a subquery which now takes about 4-5 seconds to fetch.

This is unacceptable to me. Here is the sql (the numbers are the given x and y coordinates):

SELECT concat(WAYS.id, '|',WAYS.name) "id", POINTS.x, POINTS.y 
FROM WAYS, WAYPOINTS, POINTS 
WHERE WAYPOINTS.WID = WAYS.ID AND 
      WAYPOINTS.PID = POINTS.ID AND 
      WAYPOINTS.WID IN (SELECT w.ID 
                        FROM WAYS w, WAYPOINTS wp, POINTS p 
                        WHERE wp.WID = w.ID AND wp.PID = p.ID 
                        AND p.x < (51.739400 + 0.01) 
                        AND p.x > (51.739400 - 0.01)
                        AND p.y < (8.710202 + 0.01)
                        AND p.y > (8.710202 - 0.01);

If I could at least achieve max 1 second that would be nice. I know Im asking a lot since the solution is working already but I feel like there must be something I'm missing here..

PS: Ive added an Index to each column in question.

Upvotes: 0

Views: 62

Answers (4)

wildplasser
wildplasser

Reputation: 44250

Alternative: Join on bridge-table, using exists(). This will avoid duplicates in the final result.

SELECT concat(w.id, '|',w.name) "id", p.x, p.y 
FROM WAYS w
JOIN POINTS p ON EXISTS ( -- Join on bridge-table
    SELECT * 
    FROM  WAYPOINTS wp
    WHERE wp.WID = w.ID 
    AND wp.PID = p.ID
    AND EXISTS ( -- waypoints that contain a particular point
            SELECT*
            FROM WAYPOINTS wp2
            JOIN  POINTS p2 ON wp2.PID = p2.ID
            WHERE wp2.WID = wp.WID -- same way as outer
            AND p2.x < 51.739400 + 0.01
            AND p2.x > 51.739400 - 0.01
            AND p2.y < 8.710202 + 0.01
            AND p2.y > 8.710202 - 0.01
            )
    );

Upvotes: 0

Uueerdo
Uueerdo

Reputation: 15951

SELECT p.*
FROM points AS pWithin
INNER JOIN waypoints AS pwWP ON pWithin.ID = pwWP.PID
INNER JOIN way AS pwW ON pwWP.WID = pwW.ID
INNER JOIN waypoints AS wp ON pwW.ID = wp.WID
INNER JOIN points AS p ON wp.PID = p.ID
WHERE pWithin.x < (51.739400 + 0.01) 
   AND pWithin.x > (51.739400 - 0.01)
   AND pWithin.y < (8.710202 + 0.01)
   AND pWithin.y > (8.710202 - 0.01);

The WHERE and first reference to points finds the "points within the distance", the next two joins are used to find the ways those points are in, and the final two joins find the points within those ways.

Obviously, you can replace p.* with the actual result data you want.

Edit: One thing you may need to watch out for is if a way has multiple points "within the distance" it, and all it's points will show up multiple times. Simply changing SELECT to SELECT DISTINCT should remedy that easily though.

Upvotes: 1

nsring
nsring

Reputation: 11

Yes, have you tried creating that subquery as a CTE?

For example you'd write something like

WITH (SELECT w.ID 
                    FROM WAYS w, WAYPOINTS wp, POINTS p 
                    WHERE wp.WID = w.ID AND wp.PID = p.ID 
                    AND p.x < (51.739400 + 0.01) 
                    AND p.x > (51.739400 - 0.01)
                    AND p.y < (8.710202 + 0.01)
                    AND p.y > (8.710202 - 0.01) as CTE
SELECT concat(WAYS.id, '|',WAYS.name) "id", POINTS.x, POINTS.y 
FROM WAYS, WAYPOINTS, POINTS, cte
WHERE WAYPOINTS.WID = WAYS.ID AND 
  WAYPOINTS.PID = POINTS.ID AND 
  WAYPOINTS.WID = cte.id

You'll want to compare the performance of your query to the CTE query;

To do so you'll write 

EXPLAIN ANALYZE ( 
*the entire query*
);

Upvotes: 0

ebpwt02
ebpwt02

Reputation: 11

Not positive this is what you are asking. Try this though. You should only get the Waypoints where those point criteria are true anyways.

 SELECT concat(WAYS.id, '|',WAYS.name) "id", POINTS.x, POINTS.y 
    FROM WAYS, WAYPOINTS, POINTS 
    WHERE WAYPOINTS.WID = WAYS.ID AND 
          WAYPOINTS.PID = POINTS.ID AND 
          AND Points.x < (51.739400 + 0.01) 
          AND points.x > (51.739400 - 0.01)
          AND points.y < (8.710202 + 0.01)
          AND points.y > (8.710202 - 0.01);

Upvotes: 0

Related Questions