2

i have a big problem with my database..

I need a very fast query over 2 tables. 1. table: (node) ~42.500.000 entrys 2. table: (waynode ~51.200.000 entrys

The following query is much to slow:

SELECT  `node`.ID,`node`.LATITUDE,`node`.LONGITUDE,`waynode`.WAYID
    FROM  `node`, `waynode`
    WHERE  `node`.ID= `waynode`.NODEID
      AND  `node`.LATITUDE BETWEEN '49.558' AND '49.568'
      AND  `node`.LONGITUDE BETWEEN '11.3395' AND '11.3495'
    ORDER BY  `waynode`.WAYID 

The current time is over 180 seconds..

I also have a INDEX over 2 columns created with ALTER TABLE node ADD INDEX (LATITUDE, LONGITUDE). When i am using the following select, it's very fast:

SELECT  `node`.ID,`node`.LATITUDE,`node`.LONGITUDE
    FROM  `node`
    WHERE  `node`.LATITUDE BETWEEN '49.558' AND '49.568'
      AND  `node`.LONGITUDE BETWEEN '11.3395' AND '11.3495' 

But I need the waynode.NODEID like in the first SQL-Statement. May someone help me please?

best regards Ingo

0

See if this runs faster:

SELECT  n.ID, n.LATITUDE, n.LONGITUDE,
        wn.WAYID
    FROM  
      ( SELECT  ID, LATITUDE, LONGITUDE
            FROM  `node`
            WHERE  LATITUDE  BETWEEN '49.558'  AND '49.568'
              AND  LONGITUDE BETWEEN '11.3395' AND '11.3495' 
      ) AS n
    JOIN  `waynode` AS wn  ON n.ID= wn.NODEID
    ORDER BY  wn.WAYID 

This might also work 'fast':

SELECT  n.ID, n.LATITUDE, n.LONGITUDE, 
      ( SELECT  WAYID
            FROM  waynode
            WHERE  n.ID= NODEID 
      ) AS WayID
    FROM  `node` AS n
    WHERE  LATITUDE  BETWEEN  '49.558' AND '49.568'
      AND  LONGITUDE BETWEEN '11.3395' AND '11.3495'
    ORDER BY  WayID 

For further discussion, please provide EXPLAIN SELECT ... for any query you want to discuss and SHOW CREATE TABLE ... for the tables in question.

  • Hello Rick James, your first solution also takes over 180 seconds also the second. The speedproblem is currently by asking both tables Might there be a way of creating a specific Index for this Select? – Ingo P. Jul 23 '16 at 20:06
  • Please provide SHOW CREATE TABLE. – Rick James Jul 24 '16 at 1:53

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.