I have two tables in my database that look roughly like this:
Communication: (Calls made)
Timestamp FromIDNumber ToIDNumber GeneralLocation 2012-03-02 09:02:30 878 674 Grasslands 2012-03-02 11:30:01 456 213 Tundra 2012-03-02 07:02:12 789 654 Mountains
Timestamp IDNumber Type X Y 2012-03-02 11:02:30 379 pedestrian 32 46 2012-03-01 12:32:41 654 pedestrian 54 56 2012-03-02 07:02:16 789 pedestrian 39 52
and I want to run this query:
SELECT c.senderID, c.timestamp, m.timestamp, m.x, m.y FROM communication c JOIN movement m ON c.senderID = m.visitorID WHERE m.timestamp >= c.timestamp ORDER BY m.timestamp LIMIT 1;
Basically, I want to find the closest movement timestamp to a given communication timestamp.
The thing is, these tables have millions of entries and I need to use indexes. The problem is, I'm new to SQL, and I'm not sure exactly how to structure my indexes.... would I need to have one for m.timestamp and c.timestamp separately like this?
CREATE INDEX mtstamp ON DBName.movement (timestamp); CREATE INDEX ctstamp ON DBName.communication (timestamp);
Any help would be greatly appreciated, thank you!!