Mysql – best way for creating Index in thesql (multi-column/table)

indexindex-tuningMySQL

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

Best Answer

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.

Related Question