2

I have a little problem right here...

So all in all I have exactly two tables:

* resources (ID, resourcetype_ID, chunk_ID, position)

* chunks (ID, x, y)

The first one stores some rows which belongs to the certain chunk. The second one just stores all the chunks with a different set of x and y as some kind of position.

My goal is to select all the resources that belong to a certain range of chunks. But I have made a mistake somewhere. I always get the wrong x and y positions, when I select the resources.

SELECT resources.*, chunks.x, chunks.y 
FROM resources, chunks 
where chunk_ID in (SELECT ID 
                   FROM chunks 
                   where x between 17185-1 and 17185+1 
                     and y between 10846-1 and 10846+1) 
group by ID

This query above returns all the resources that belongs to the different chunks. But the returned x and y position is for all of them the same. Why does this happen?

(As you can see the resources with "chunk_ID" = 1 have the same x and y as the resources with "chunk_ID" = 5 ... even if those chunks have completely different x and y pairs...)

enter image description here

Normally all resources should have different x and y values regarding to the chunk they belong to.

  • Do not store latitude/longtitude that way. Dedicate a FLOAT(9,6) column for each coordinate instead. – Kondybas Jun 4 '18 at 18:24
3

Your query:

SELECT resources.*, chunks.x, chunks.y 
FROM resources, chunks 
where chunk_ID in (SELECT ID 
                   FROM chunks 
                   where x between 17185-1 and 17185+1 
                     and y between 10846-1 and 10846+1) 
group by ID

I see several issues:

  • The two tables in the FROM clause are not joined (that's the reason for the irrelevant results you get, the two tables are correlated through the subquery* but the FROM clause introduces an additional non-correlated chunks reference which produces all these unrelated results):

    FROM resources, chunks 
    

    Join them:

    FROM resources JOIN chunks ON resources.chunk_id = chunks.id 
    
  • Since chunks is in the FROM clause, we don't need* the subquery no more, to check x and y:

    where chunk_ID in (SELECT ID 
                       FROM chunks 
                       where x between 17185-1 and 17185+1 
                         and y between 10846-1 and 10846+1) 
    

    Simplify WHERE:

    WHERE chunks.x BETWEEN 17185-1 AND 17185+1 
      AND chunks.y BETWEEN 10846-1 AND 10846+1
    
  • You have SELECT * combined with GROUP BY. This is a recipe for spurious, wrong results.

    Just remove the GROUP BY.

The query becomes after using all the above comments (and some polishing with table aliases):

-- Suggested query
SELECT r.*, c.x, c.y 
FROM resources AS r
     JOIN chunks AS c
     ON r.chunk_id = c.id  
WHERE c.x BETWEEN 17185-1 AND 17185+1 
  AND c.y BETWEEN 10846-1 AND 10846+1 ;

* Just for completeness, we could have kept the IN subquery and remove chunks from the FROM clause.

This would have the restriction that we couldn't have any column from chunks in the SELECT list - and more likely worse performance than the JOIN query:

-- Don't use this one
SELECT r.* 
FROM resources AS r
WHERE r.chunk_d IN 
      ( SELECT c.id
        FROM chunks AS c
        WHERE c.x BETWEEN 17185-1 AND 17185+1
          AND c.y BETWEEN 10846-1 AND 10846+1
      ) ;
  • The only addition is that values for BETWEEN should be precalculated to consts: BETWEEN 17184 AND 17186. Otherwise index may become unused. – Kondybas Jun 4 '18 at 20:46
  • Indexes can be used. It doesn't make a difference if we have 17184 or 17185-1. – ypercubeᵀᴹ Jun 4 '18 at 20:47
  • With mysql 5.3 I have alot of happiness until traced to that issue. Now I prefer to have consts everywhere where indexes are involved. – Kondybas Jun 4 '18 at 20:53
  • 1
    @Kondybas - False: Expressions that evaluate to a constant are done before deciding on indexing. That is, MySQL is smart enough to turn 17185-1 into 17184. I tried WHERE id BETWEEN 1111-1 AND 222222+1; the Optimizer trace said "... index": "id", "ranges": ["1110 <= id <= 222223"], ... (Possibly it was an improvement since 5.3.) Wait; what 5.3?? – Rick James Jun 6 '18 at 1:39
  • @Rick James It is a typo, 5.4 certainly. – Kondybas Jun 9 '18 at 7:54

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.