Optimized way of searching for users' locations at a given date

[SirRealism]

   

I'm sure the SQL I'm after is simple, but I just can't get my head around it.

 

I have just two tables in question: users and nodes.  Each user will have many nodes, each node representing a specific location that user lived at a given time.  Each node has a begin date, but no end date, assuming that the user was in that location until the next node begin date.

 

I'm seeking a SELECT statement to find all the users who live with X miles of a particular user at a particular point in time.  I already have the distance calculation based on lat/long working.  And in fact, I've come up with a SELECT on the users table that accomplishes what I need:

 

(assume that the lat/long and date are fed in as constants 39.1653, -86.5264, and 1987-08-01 respectively)

SELECT

(SELECT n.id FROM nodes n WHERE n.date <= '1987-08-01' AND n.user_id = u.id ORDER BY n.date DESC LIMIT 1) AS node_id,

(SELECT (SQRT( POW( 69.1 * ( n.lat - 39.1653 ) , 2 ) + POW( 69.1 * ( -86.5264 - n.long ) * COS( n.lat / 57.3 ) , 2 ) ) ) FROM nodes n WHERE n.date <= '1987-08-01' AND n.user_id = u.id ORDER BY n.date DESC LIMIT 1) AS distance,

u.*

FROM users u

WHERE

true

HAVING

distance < 50

;

 

However, it seems to me that having both of these subselects is inefficient if I get lots of users and lots of nodes... especially calling the same WHEREs and LIMITs in bold.  It seems to me that it would be more efficient to do the primary SELECT on `nodes`, but I can't figure out the GROUP BY and WHERE of it all.

 

Is there a more efficient way to do this?

 

-------------------------------------------------------

 

Specifics:

 

MySQL v5.1.37

 

CREATE TABLE `users` (

  `id` int(11) NOT NULL AUTO_INCREMENT,

  `email` varchar(100) NOT NULL,

  `password` char(32) NOT NULL,

  `name_last` varchar(50) NOT NULL,

  `name_first` varchar(50) NOT NULL,

  PRIMARY KEY (`id`),

  KEY `name_last` (`name_last`,`name_first`)

) ENGINE=MyISAM AUTO_INCREMENT=5 DEFAULT CHARSET=latin1

 

CREATE TABLE `nodes` (

  `id` int(11) NOT NULL AUTO_INCREMENT,

  `user_id` int(11) NOT NULL,

  `description` varchar(100) NOT NULL,

  `date` date NOT NULL,

  `lat` decimal(10,4) NOT NULL,

  `long` decimal(10,4) NOT NULL,

  PRIMARY KEY (`id`),

  KEY `user_id` (`user_id`,`lat`,`long`),

  KEY `date` (`date`)

) ENGINE=MyISAM AUTO_INCREMENT=23 DEFAULT CHARSET=latin1