Myujth

I have two tables, one containing data the other is metadata.

The main data table contains a grid of geospatial coordinates (up to billions of rows). The coordinates are projected to a specific coordinate system. The relevant part of the schema is:

 ------------------
| x | smallint |
|------------------|
| y | smallint |
|------------------|
| value | string |
 ------------------

The metadata table contains the corresponding latitude and longitude values for the x, y coordinates. The relevant part of the schema is:

 ----------------------------
| x | smallint |
|----------------------------|
| y | smallint |
|----------------------------|
| latitude | decimal(18,15) |
|----------------------------|
| longitude | decimal(18,15) |
 ----------------------------

A JOIN on these two tables allows one to know the actual latitude/longitude for a particular X/Y coordinate. This will make queries on the table easier since you don't need to know anything about the map projection.

A sample query that retrieves rows from the table:

SELECT 
 main.x,
 main.y,
 latitude,
 longitude,
 value
FROM database.main JOIN database.meta
ON main.x=meta.x AND main.y=meta.y
WHERE
 main.x=1 AND main.y<=2

The result set would look like:

 --------------------------------------
| x | y | latitude | longitude | value |
|--------------------------------------|
| 1 | 1 | 12.345 | 54.321 | row1 |
|--------------------------------------|
| 1 | 2 | 12.345 | 98.765 | row2 |
 --------------------------------------

This query only takes 1-2 seconds, great!

My problem is, when I run a query that adds a WHERE clause comparing latitude and longitudes, the query works but it takes over 60 seconds to run...

For example:

SELECT 
 main.x,
 main.y,
 latitude,
 longitude,
 value
FROM database.main JOIN database.meta
ON main.x=meta.x AND main.y=meta.y
WHERE
 latitude=DECIMAL '12.345' AND longitude=DECIMAL '98.765'

I know that I am probably missing something fundamental about SQL and JOINs that is causing this query to be slow. Independent queries on either table are ultra fast so I know I'm doing something wrong here that has to do with the JOIN.

So the question is, how do I make it so that this (seemingly) simple comparison completes in only a few seconds rather than over 60 seconds?

The most likely explanation for poor performance when adding a JOIN is that there is no index that can be used to optimize the join.

In your case, specifying on index on main(x,y) is most likely to speed up the JOIN.