--disable_warnings
--disable_query_log
--disable_result_log

DROP TABLE IF EXISTS `InMemoryDIASource`;
CREATE TABLE `InMemoryDIASource` (
  `id` bigint(20) NOT NULL,
  `zoneId` int(11) NOT NULL,
  `ra` double NOT NULL,
  `decl` double NOT NULL,
  `x` double NOT NULL,
  `y` double NOT NULL,
  `z` double NOT NULL,
  PRIMARY KEY  (`id`),
  KEY `idx_zone_ra` USING HASH (`zoneId`,`ra`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1;

LOCK TABLES `InMemoryDIASource` WRITE;
INSERT INTO `InMemoryDIASource` VALUES (35616,0,0,0,0,0,0),(35618,0,0,0,0,0,0),(35619,0,0,0,0,0,0),(35621,0,0,0,0,0,0),(35629,0,0,0,0,0,0),(35634,0,0,0,0,0,0);
UNLOCK TABLES;

DROP TABLE IF EXISTS `InMemoryObject`;
CREATE TABLE `InMemoryObject` (
  `id` bigint(20) NOT NULL,
  `ra` decimal(8,5) NOT NULL,
  `decl` decimal(8,5) NOT NULL,
  `zoneId` int(11) NOT NULL default '0',
  `x` double NOT NULL default '0',
  `y` double NOT NULL default '0',
  `z` double NOT NULL default '0',
  PRIMARY KEY  (`id`),
  KEY `idx_zone_ra` USING BTREE (`zoneId`,`ra`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1;

LOCK TABLES `InMemoryObject` WRITE;
INSERT INTO `InMemoryObject` VALUES (706023027,'0.00000','0.00000',0,0,0,0),(706023028,'0.00000','0.00000',0,0,0,0),(706023029,'0.00000','0.00000',0,0,0,0),(706023030,'0.00000','0.00000',0,0,0,0),(706023243,'0.00000','0.00000',0,0,0,0),(706023244,'0.00000','0.00000',0,0,0,0),(706023245,'0.00000','0.00000',0,0,0,0),(706023246,'0.00000','0.00000',0,0,0,0),(706023247,'0.00000','0.00000',0,0,0,0);
UNLOCK TABLES;

DROP TABLE IF EXISTS `ZoneZone`;
CREATE TABLE `ZoneZone` (
  `zoneId` int(11) NOT NULL,
  `matchZoneId` int(11) NOT NULL,
  `deltaRa` double NOT NULL,
  PRIMARY KEY  USING BTREE (`zoneId`,`matchZoneId`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1;

LOCK TABLES `ZoneZone` WRITE;
INSERT INTO `ZoneZone` VALUES (5000,0,0),(5001,0,0),(5002,0,0),(5003,0,0),(5004,0,0),(5005,0,0),(5006,0,0),(5007,0,0),(5008,0,0),(5009,0,0),(5010,0,0),(5011,0,0),(5012,0,0),(5013,0,0),(5014,0,0),(5015,0,0),(5016,0,0),(5017,0,0),(5018,0,0),(5019,0,0),(5020,0,0),(5021,0,0),(5022,0,0),(5023,0,0),(5024,0,0),(5025,0,0),(5026,0,0),(5027,0,0),(5028,0,0),(5029,0,0),(5030,0,0),(5031,0,0),(5032,0,0),(5033,0,0),(5034,0,0),(5035,0,0),(5036,0,0),(5037,0,0),(5038,0,0),(5039,0,0),(5040,0,0),(5041,0,0),(5042,0,0),(5043,0,0),(5044,0,0),(5045,0,0),(5046,0,0),(5047,0,0),(5048,0,0),(5049,0,0),(5050,0,0),(5051,0,0),(5052,0,0),(5053,0,0),(5054,0,0),(5055,0,0),(5056,0,0),(5057,0,0),(5058,0,0),(5059,0,0),(5060,0,0),(5061,0,0),(5062,0,0),(5063,0,0),(5064,0,0),(5065,0,0),(5066,0,0),(5067,0,0),(5068,0,0),(5069,0,0),(5070,0,0),(5071,0,0),(5072,0,0),(5073,0,0),(5074,0,0),(5075,0,0),(5076,0,0),(5077,0,0),(5078,0,0),(5079,0,0),(5080,0,0),(5081,0,0),(5082,0,0),(5083,0,0),(5084,0,0),(5085,0,0),(5086,0,0),(5087,0,0),(5088,0,0),(5089,0,0),(5090,0,0),(5091,0,0),(5092,0,0),(5093,0,0),(5094,0,0),(5095,0,0),(5096,0,0),(5097,0,0),(5098,0,0),(5099,0,0),(5100,0,0),(5101,0,0),(5102,0,0),(5103,0,0),(5104,0,0),(5105,0,0),(5106,0,0),(5107,0,0),(5108,0,0),(5109,0,0),(5110,0,0),(5111,0,0),(5112,0,0),(5113,0,0),(5114,0,0),(5115,0,0),(5116,0,0),(5117,0,0),(5118,0,0),(5119,0,0),(5120,0,0),(5121,0,0),(5122,0,0),(5123,0,0),(5124,0,0),(5125,0,0),(5126,0,0),(5127,0,0),(5128,0,0),(5129,0,0),(5130,0,0),(5131,0,0),(5132,0,0),(5133,0,0),(5134,0,0),(5135,0,0),(5136,0,0),(5137,0,0),(5138,0,0),(5139,0,0),(5140,0,0),(5141,0,0),(5142,0,0),(5143,0,0),(5144,0,0),(5145,0,0),(5146,0,0),(5147,0,0),(5148,0,0),(5149,0,0),(5150,0,0),(5151,0,0),(5152,0,0),(5153,0,0),(5154,0,0),(5155,0,0),(5156,0,0),(5157,0,0),(5158,0,0),(5159,0,0),(5160,0,0),(5161,0,0),(5162,0,0),(5163,0,0),(5164,0,0),(5165,0,0),(5166,0,0),(5167,0,0),(5168,0,0),(5169,0,0),(5170,0,0),(5171,0,0),(5172,0,0),(5173,0,0),(5174,0,0),(5175,0,0),(5176,0,0),(5177,0,0),(5178,0,0),(5179,0,0),(5180,0,0),(5181,0,0),(5182,0,0),(5183,0,0),(5184,0,0),(5185,0,0),(5186,0,0),(5187,0,0),(5188,0,0),(5189,0,0),(5190,0,0),(5191,0,0),(5192,0,0),(5193,0,0),(5194,0,0),(5195,0,0),(5196,0,0),(5197,0,0),(5198,0,0),(5199,0,0),(5200,0,0),(5201,0,0),(5202,0,0),(5203,0,0),(5204,0,0),(5205,0,0),(5206,0,0),(5207,0,0),(5208,0,0),(5209,0,0),(5210,0,0),(5211,0,0),(5212,0,0),(5213,0,0),(5214,0,0),(5215,0,0),(5216,0,0),(5217,0,0),(5218,0,0),(5219,0,0),(5220,0,0),(5221,0,0),(5222,0,0),(5223,0,0),(5224,0,0),(5225,0,0),(5226,0,0),(5227,0,0),(5228,0,0),(5229,0,0),(5230,0,0),(5231,0,0),(5232,0,0),(5233,0,0),(5234,0,0),(5235,0,0),(5236,0,0),(5237,0,0),(5238,0,0),(5239,0,0),(5240,0,0),(5241,0,0),(5242,0,0),(5243,0,0),(5244,0,0),(5245,0,0),(5246,0,0),(5247,0,0),(5248,0,0),(5249,0,0),(5250,0,0),(5251,0,0),(5252,0,0),(5253,0,0),(5254,0,0),(5255,0,0),(5256,0,0),(5257,0,0),(5258,0,0),(5259,0,0),(5260,0,0),(5261,0,0),(5262,0,0),(5263,0,0),(5264,0,0),(5265,0,0),(5266,0,0),(5267,0,0),(5268,0,0),(5269,0,0),(5270,0,0),(5271,0,0),(5272,0,0),(5273,0,0),(5274,0,0),(5275,0,0),(5276,0,0),(5277,0,0),(5278,0,0),(5279,0,0),(5280,0,0),(5281,0,0),(5282,0,0),(5283,0,0),(5284,0,0),(5285,0,0),(5286,0,0),(5287,0,0),(5288,0,0),(5289,0,0),(5290,0,0),(5291,0,0),(5292,0,0),(5293,0,0),(5294,0,0),(5295,0,0),(5296,0,0),(5297,0,0),(5298,0,0),(5299,0,0),(5300,0,0),(5301,0,0),(5302,0,0),(5303,0,0),(5304,0,0),(5305,0,0),(5306,0,0),(5307,0,0),(5308,0,0),(5309,0,0),(5310,0,0),(5311,0,0),(5312,0,0),(5313,0,0),(5314,0,0),(5315,0,0),(5316,0,0),(5317,0,0),(5318,0,0),(5319,0,0),(5320,0,0),(5321,0,0),(5322,0,0),(5323,0,0),(5324,0,0),(5325,0,0),(5326,0,0),(5327,0,0),(5328,0,0),(5329,0,0),(5330,0,0),(5331,0,0),(5332,0,0),(5333,0,0),(5334,0,0),(5335,0,0),(5336,0,0),(5337,0,0),(5338,0,0),(5339,0,0),(5340,0,0),(5341,0,0),(5342,0,0),(5343,0,0),(5344,0,0),(5345,0,0),(5346,0,0),(5347,0,0),(5348,0,0),(5349,0,0),(5350,0,0),(5351,0,0),(5352,0,0),(5353,0,0),(5354,0,0),(5355,0,0),(5356,0,0),(5357,0,0),(5358,0,0),(5359,0,0),(5360,0,0),(5361,0,0),(5362,0,0),(5363,0,0),(5364,0,0),(5365,0,0),(5366,0,0),(5367,0,0),(5368,0,0),(5369,0,0),(5370,0,0),(5371,0,0),(5372,0,0),(5373,0,0),(5374,0,0),(5375,0,0),(5376,0,0),(5377,0,0),(5378,0,0),(5379,0,0),(5380,0,0),(5381,0,0),(5382,0,0),(5383,0,0),(5384,0,0),(5385,0,0),(5386,0,0),(5387,0,0),(5388,0,0),(5389,0,0),(5390,0,0),(5391,0,0),(5392,0,0),(5393,0,0),(5394,0,0),(5395,0,0),(5396,0,0),(5397,0,0),(5398,0,0),(5399,0,0);
UNLOCK TABLES;

--enable_result_log
--enable_query_log
--enable_warnings

EXPLAIN SELECT p.id, s.id FROM InMemoryDIASource AS p
    INNER JOIN ZoneZone AS zz FORCE INDEX (PRIMARY) ON p.zoneId = zz.zoneId
    INNER JOIN InMemoryObject AS s FORCE INDEX (idx_zone_ra) ON zz.matchZoneId = s.zoneId
    WHERE s.ra BETWEEN p.ra - zz.deltaRa AND p.ra + zz.deltaRa
    AND s.decl BETWEEN p.decl - 0.000833 AND p.decl + 0.000833
    AND POW(p.x - s.x, 2) + POW(p.y - s.y, 2) + POW(p.z - s.z, 2) < 2.1137067679466e-10;
	
EXPLAIN SELECT STRAIGHT_JOIN p.id, s.id FROM InMemoryDIASource AS p
    INNER JOIN ZoneZone AS zz FORCE INDEX (PRIMARY) ON p.zoneId = zz.zoneId
    INNER JOIN InMemoryObject AS s FORCE INDEX (idx_zone_ra) ON zz.matchZoneId = s.zoneId
    WHERE s.ra BETWEEN p.ra - zz.deltaRa AND p.ra + zz.deltaRa
    AND s.decl BETWEEN p.decl - 0.000833 AND p.decl + 0.000833
    AND POW(p.x - s.x, 2) + POW(p.y - s.y, 2) + POW(p.z - s.z, 2) < 2.1137067679466e-10;
	
EXPLAIN SELECT p.id, s.id FROM InMemoryDIASource AS p
    INNER JOIN ZoneZone AS zz FORCE INDEX (PRIMARY) ON p.zoneId = zz.zoneId
    INNER JOIN InMemoryObject AS s FORCE INDEX (idx_zone_ra) ON
        zz.matchZoneId = s.zoneId AND
        s.ra BETWEEN p.ra - zz.deltaRa AND p.ra + zz.deltaRa
    WHERE s.decl BETWEEN p.decl - 0.000833 AND p.decl + 0.000833
    AND POW(p.x - s.x, 2) + POW(p.y - s.y, 2) + POW(p.z - s.z, 2) < 2.1137067679466e-10;
	
EXPLAIN SELECT STRAIGHT_JOIN p.id, s.id FROM InMemoryDIASource AS p
    INNER JOIN ZoneZone AS zz FORCE INDEX (PRIMARY) ON p.zoneId = zz.zoneId
    INNER JOIN InMemoryObject AS s FORCE INDEX (idx_zone_ra) ON
        zz.matchZoneId = s.zoneId AND
        s.ra BETWEEN p.ra - zz.deltaRa AND p.ra + zz.deltaRa
    WHERE s.decl BETWEEN p.decl - 0.000833 AND p.decl + 0.000833
    AND POW(p.x - s.x, 2) + POW(p.y - s.y, 2) + POW(p.z - s.z, 2) < 2.1137067679466e-10;