Pastebin launched a little side project called VERYVIRAL.com, check it out ;-) Want more features on Pastebin? Sign Up, it's FREE!
Guest

Untitled

By: a guest on Jan 4th, 2013  |  syntax: None  |  size: 16.76 KB  |  views: 15  |  expires: Never
download  |  raw  |  embed  |  report abuse  |  print
Text below is selected. Please press Ctrl+C to copy to your clipboard. (⌘+C on Mac)
  1. SELECT
  2.     table1.*,
  3.     table2.*
  4. FROM
  5.     Table1 AS table1
  6. LEFT JOIN
  7.     Table2 AS table2
  8. USING
  9.     (col1)
  10. LEFT JOIN
  11.     Table3 as table3
  12. USING
  13.     (col1)
  14. WHERE
  15.     3963.191 *
  16.     ACOS(
  17.     (SIN(PI() * $usersLatitude / 180) * SIN(PI() * table3.latitude / 180))
  18.     +
  19.     (COS(PI() * $usersLatitude / 180) * COS(PI() * table3.latitude / 180) * COS(PI() * table3.longitude / 180 - PI() * 37.1092162 / 180))
  20.     ) <= 10
  21. AND
  22.     table1.col1 != '1'
  23. AND
  24.     table1.col2 LIKE 'A'
  25. AND
  26.     (table1.col3 LIKE 'X' OR table1.col3 LIKE 'X-Y')
  27. AND
  28.     (table2.col4 = 'Y' OR table2.col5 = 'Y')
  29.  
  30.  
  31. // Data Types of all columns in the query:
  32. // col1: int(11)
  33. // col2: char(1)
  34. // col3: varchar(3)
  35. // col4: char(1)
  36. // col5: char(1)
  37. // col6: int(11)
  38. // latitude: varchar(25)
  39. // longitude: varchar(25)
  40.  
  41. // All 3 tables (table1, table2, and table3) are `MyISAM`.
  42.        
  43. ORDER BY
  44.     table1.col6 DESC
  45.        
  46. id  select_type table   type    possible_keys   key key_len ref rows    filtered    Extra
  47. 1   SIMPLE  table1  ALL PRIMARY,col2,col3   NULL    NULL    NULL    140101  72.61   Using where
  48. 1   SIMPLE  table2  eq_ref  PRIMARY,col4,col5   PRIMARY 4   table1.col1 1   100 Using where
  49. 1   SIMPLE  table3  eq_ref  PRIMARY PRIMARY 4   table1.col1 1   100 Using where
  50.        
  51. id  select_type table   type    possible_keys   key key_len ref rows    filtered    Extra
  52. 1   SIMPLE  table1  ALL PRIMARY,col2,col3   NULL    NULL    NULL    140101  72.61   Using where; Using filesort
  53. 1   SIMPLE  table2  eq_ref  PRIMARY,col4,col5   PRIMARY 4   table1.col1 1   100 Using where
  54. 1   SIMPLE  table3  eq_ref  PRIMARY PRIMARY 4   table1.col1 1   100 Using where
  55.        
  56. SELECT STRAIGHT_JOIN
  57.       T1.*,
  58.       T2.*
  59.    FROM
  60.       Table1 AS T1
  61.          JOIN Table2 AS T2
  62.             ON T1.Col1 = T2.Col1
  63.             AND ( T2.Col4 = 'Y' OR T2.Col5 = 'Y' )
  64.          JOIN Table3 as T3
  65.             ON T1.Col1 = T3.Col1
  66.             AND 3963.191
  67.                * ACOS(  (SIN(PI() * $usersLatitude / 180) * SIN(PI() * T3.latitude / 180))
  68.                                 + (  COS(PI() * $usersLatitude / 180) * COS(PI() * T3.latitude / 180)
  69.                                    * COS(PI() * table3.longitude / 180 - PI() * 37.1092162 / 180)
  70.                         )  
  71.                      ) <= 10
  72.    WHERE
  73.           T1.Col2 LIKE 'A'
  74.       AND ( T1.col3 LIKE 'X' OR T1.col3 LIKE 'X-Y')
  75.       AND T1.Col1 != '1'
  76.    ORDER BY
  77.       T1.Col6
  78.  
  79. // With the following composite indexes:
  80. // On Table 1, index on ( Col2, Col3, Col1, Col6 )
  81. // On Table 2, index on ( Col1, Col4, Col5 )
  82.  
  83. // Remember, all individual columns are already indexed.
  84.        
  85. CREATE TABLE `Table1` (
  86.  `col1` int(11) unsigned NOT NULL AUTO_INCREMENT,
  87.  `col100` varchar(25) CHARACTER SET utf8 DEFAULT NULL,
  88.  `col101` varchar(60) COLLATE utf8_bin DEFAULT NULL,
  89.  `col102` varchar(50) CHARACTER SET utf8 DEFAULT NULL,
  90.  `col103` varchar(10) COLLATE utf8_bin DEFAULT '00000000',
  91.  `col104` date NOT NULL,
  92.  `col105` int(3) DEFAULT NULL,
  93.  `col106` varchar(25) COLLATE utf8_bin DEFAULT NULL,
  94.  `col107` varchar(20) COLLATE utf8_bin DEFAULT 'Blah',
  95.  `col108` varchar(2) COLLATE utf8_bin DEFAULT 'No',
  96.  `col109` varchar(15) COLLATE utf8_bin DEFAULT 'Blah',
  97.  `col2` enum('A','B') COLLATE utf8_bin DEFAULT NULL,
  98.  `col3` enum('A','B','A-B') COLLATE utf8_bin DEFAULT NULL,
  99.  `col110` decimal(10,7) NOT NULL DEFAULT '0.0000000',
  100.  `col111` decimal(10,7) NOT NULL DEFAULT '0.0000000',
  101.  `col112` char(1) COLLATE utf8_bin DEFAULT 'N',
  102.  `col113` char(1) COLLATE utf8_bin DEFAULT 'N',
  103.  `col114` int(11) DEFAULT NULL,
  104.  `col115` varchar(15) COLLATE utf8_bin DEFAULT 'Blah',
  105.  `col6` int(11) DEFAULT NULL,
  106.  `col117` varchar(45) COLLATE utf8_bin DEFAULT NULL,
  107.  `col118` varchar(2) COLLATE utf8_bin NOT NULL,
  108.  `col119` tinyint(2) NOT NULL,
  109.  `col120` int(6) NOT NULL,
  110.  `col121` varchar(7) COLLATE utf8_bin NOT NULL,
  111.  `col122` varchar(6) COLLATE utf8_bin NOT NULL,
  112.  `col123` char(1) COLLATE utf8_bin NOT NULL DEFAULT 'A',
  113.  `col124` varchar(200) COLLATE utf8_bin NOT NULL,
  114.  `col125` tinyint(4) NOT NULL,
  115.  `col126` tinyint(1) NOT NULL,
  116.  `col127` varchar(1) COLLATE utf8_bin NOT NULL DEFAULT 'A',
  117.  `col128` tinyint(1) NOT NULL DEFAULT '0',
  118.  `col129` smallint(5) unsigned NOT NULL,
  119.  `col130` varchar(1) COLLATE utf8_bin NOT NULL DEFAULT 'A',
  120.  `col131` int(11) NOT NULL,
  121.  `col132` tinyint(1) NOT NULL,
  122.  `col133` tinyint(1) NOT NULL,
  123.  `col134` varchar(1) COLLATE utf8_bin NOT NULL,
  124.  `col135` varchar(200) COLLATE utf8_bin NOT NULL,
  125.  `col136` int(11) NOT NULL,
  126.  `col137` int(10) unsigned NOT NULL,
  127.  `col138` int(11) NOT NULL,
  128.  `col139` tinyint(1) NOT NULL,
  129.  `col140` tinyint(1) NOT NULL,
  130.  `col141` tinyint(4) NOT NULL,
  131.  `col142` varchar(25) COLLATE utf8_bin NOT NULL,
  132.  `col143` varchar(25) COLLATE utf8_bin NOT NULL,
  133.  `col144` tinyint(1) unsigned NOT NULL,
  134.  `col145` tinyint(4) NOT NULL,
  135.  PRIMARY KEY (`col1`),
  136.  KEY `col2` (`col2`),
  137.  KEY `col3` (`col3`),
  138.  KEY `CompositeIndex0` (`col1`,`col2`,`col3`,`col6`),
  139.  KEY `CompositeIndex1` (`col2`,`col3`,`col1`,`col6`),
  140.  KEY `idx01` (`col1`,`col2`,`col3`)
  141.  [19 other indexes that do not involve col1, col2, col3, or col6...]
  142. ) ENGINE=MyISAM AUTO_INCREMENT=160640 DEFAULT CHARSET=utf8 COLLATE=utf8_bin
  143.  
  144. //*******************************************************//
  145.  
  146. CREATE TABLE `Table2` (
  147.  `col1` int(11) unsigned NOT NULL DEFAULT '0',
  148.  `col201` varchar(45) CHARACTER SET utf8 COLLATE utf8_unicode_ci DEFAULT 'Blah',
  149.  `col202` varchar(45) CHARACTER SET utf8 COLLATE utf8_unicode_ci DEFAULT 'Blah',
  150.  `col203` varchar(45) COLLATE utf8_bin DEFAULT 'Blah',
  151.  `col204` varchar(45) COLLATE utf8_bin DEFAULT 'Blah',
  152.  `col205` varchar(45) COLLATE utf8_bin DEFAULT 'Blah',
  153.  `col206` varchar(45) COLLATE utf8_bin DEFAULT 'Blah',
  154.  `col207` varchar(45) COLLATE utf8_bin DEFAULT 'Blah',
  155.  `col208` varchar(45) COLLATE utf8_bin DEFAULT 'Blah',
  156.  `col209` varchar(45) COLLATE utf8_bin DEFAULT 'Blah',
  157.  `col210` varchar(45) COLLATE utf8_bin DEFAULT 'Blah',
  158.  `col211` varchar(45) COLLATE utf8_bin DEFAULT 'Blah',
  159.  `col212` varchar(45) COLLATE utf8_bin DEFAULT 'Blah',
  160.  `col213` varchar(45) COLLATE utf8_bin DEFAULT 'Blah',
  161.  `col214` varchar(45) COLLATE utf8_bin DEFAULT 'Blah',
  162.  `col215` varchar(45) COLLATE utf8_bin DEFAULT 'Blah',
  163.  `col216` varchar(45) COLLATE utf8_bin DEFAULT 'Blah',
  164.  `col217` varchar(45) COLLATE utf8_bin DEFAULT 'Blah',
  165.  `col218` varchar(45) COLLATE utf8_bin DEFAULT 'Blah',
  166.  `col219` varchar(45) COLLATE utf8_bin DEFAULT 'Blah',
  167.  `col220` varchar(255) COLLATE utf8_bin DEFAULT 'Blah',
  168.  `col221` varchar(255) COLLATE utf8_bin DEFAULT 'Blah',
  169.  `col222` varchar(255) COLLATE utf8_bin DEFAULT 'Blah',
  170.  `col223` varchar(255) COLLATE utf8_bin DEFAULT 'Blah',
  171.  `col224` varchar(45) COLLATE utf8_bin DEFAULT ‘Blah’,
  172.  `col225` varchar(255) COLLATE utf8_bin DEFAULT NULL,
  173.  `col4` char(1) COLLATE utf8_bin DEFAULT 'A',
  174.  `col226` char(1) COLLATE utf8_bin DEFAULT 'A',
  175.  `col227` varchar(5) COLLATE utf8_bin DEFAULT 'Blah',
  176.  `col228` char(1) COLLATE utf8_bin NOT NULL,
  177.  `col229` text COLLATE utf8_bin,
  178.  `col5` char(1) COLLATE utf8_bin DEFAULT 'A',
  179.  `col230` varchar(255) COLLATE utf8_bin DEFAULT 'Blah',
  180.  `col231` varchar(255) COLLATE utf8_bin DEFAULT NULL,
  181.  `col232` varchar(255) COLLATE utf8_bin DEFAULT NULL,
  182.  `col233` varchar(255) COLLATE utf8_bin DEFAULT NULL,
  183.  PRIMARY KEY (`col1`),
  184.  KEY `col4` (`col4`),
  185.  KEY `col5` (`col5`),
  186.  KEY `CompositeIndex1` (`col1`,`col4`,`col5`),
  187.  [4 other indexes not involving col1, col4, col5...]
  188.  FULLTEXT KEY `col220` (`col220`)
  189. ) ENGINE=MyISAM DEFAULT CHARSET=utf8 COLLATE=utf8_bin
  190.  
  191. //*******************************************************//
  192.  
  193. CREATE TABLE `Table3` (
  194.  `col1` int(11) unsigned NOT NULL DEFAULT '0',
  195.  `col300` varchar(255) COLLATE utf8_bin DEFAULT NULL,
  196.  `latitude` varchar(25) COLLATE utf8_bin NOT NULL DEFAULT '0',
  197.  `longitude` varchar(25) COLLATE utf8_bin NOT NULL DEFAULT '0',
  198.  `col301` int(11) DEFAULT NULL,
  199.  `static2` float(18,16) DEFAULT '0.0000000000000000',
  200.  `static3` float(18,16) DEFAULT '0.0000000000000000',
  201.  PRIMARY KEY (`col1`),
  202.  KEY `latitude` (`latitude`),
  203.  KEY `longitude` (`longitude`),
  204.  KEY `static2` (`static2`),
  205.  KEY `static3` (`static3`)
  206. ) ENGINE=MyISAM DEFAULT CHARSET=utf8 COLLATE=utf8_bin
  207.        
  208. # The MySQL database server configuration file.
  209.  
  210. [mysqld]
  211.  
  212. open-files-limit                = 20000
  213.  
  214. thread-cache-size               = 16
  215. table-open-cache                = 2048
  216. table-definition-cache          = 512
  217.  
  218. query-cache-type                = 1
  219. query-cache-size                = 32M
  220. query-cache-limit               = 1M
  221.  
  222. sort-buffer-size                = 1M
  223. read-buffer-size                = 1M
  224. read-rnd-buffer-size            = 8M
  225. join-buffer-size                = 1M
  226.  
  227. tmp-table-size                  = 64M
  228. max-heap-table-size             = 64M
  229.  
  230. back-log                        = 100
  231. max-connections                 = 200
  232. max-connect-errors              = 10000
  233. max-allowed-packet              = 16M
  234. interactive-timeout             = 600
  235. wait-timeout                    = 180
  236. net_read_timeout        = 30
  237. net_write_timeout       = 30
  238.  
  239. back_log            = 128
  240.  
  241. myisam-sort-buffer-size         = 128M
  242.  
  243. innodb-buffer-pool-size         = 320M
  244. innodb-log-buffer-size          = 4M
  245.  
  246. innodb-log-file-size           = 128M
  247. innodb-log-files-in-group      = 2
  248.  
  249. innodb-file-per-table           = 1
  250.  
  251. [mysqldump]
  252. max-allowed-packet      = 16M
  253.        
  254. id  select_type table   type    possible_keys   key key_len ref rows    Extra
  255. 1   SIMPLE  T1  ref PRIMARY,col2,col3,col1,CompositeIndex1,idx01    CompositeIndex1 2   const   92333   Using where; Using filesort
  256. 1   SIMPLE  T3  eq_ref  PRIMARY PRIMARY 4   T1.col1 1   Using where
  257. 1   SIMPLE  T2  eq_ref  PRIMARY,CompositeIndex1,idx_static1 PRIMARY 4   T1.col1 1   Using where
  258.        
  259. SELECT STRAIGHT_JOIN T1 . * , T2 . *
  260. FROM Table3 AS T3
  261. JOIN Table2 AS T2 ON T3.col1 = T2.col1
  262. AND (
  263. T2.col4 = 'Y'
  264. OR T2.col5 = 'Y'
  265. )
  266. JOIN Table1 AS T1 ON T3.col1 = T1.col1
  267. AND 3963.191 * ACOS( (
  268. SIN( PI( ) * - 87.8819594 /180 ) * SIN( PI( ) * T3.latitude /180 ) ) + ( COS( PI( ) * - 87.8819594 /180 ) * COS( PI( ) * T3.latitude /180 ) * COS( PI( ) * T3.longitude /180 - PI( )* 37.1092162 /180 ) )
  269. ) <=10
  270. WHERE T1.col2 LIKE 'A'
  271. AND (
  272. T1.col3 LIKE 'X'
  273. OR T1.col3 LIKE 'X-Y'
  274. )
  275. AND T1.col1 != '1'
  276. ORDER BY T1.col6 DESC
  277.        
  278. id  select_type table   type    possible_keys   key key_len ref rows    filtered    Extra
  279. 1   SIMPLE  T3  ALL PRIMARY NULL    NULL    NULL    141923  100 Using where; Using temporary; Using filesort
  280. 1   SIMPLE  T2  eq_ref  PRIMARY,col4,col5,CompositeIndex1   PRIMARY 4   T3.col1 1   100 Using where
  281. 1   SIMPLE  T1  eq_ref  PRIMARY,col2,col3,col1,CompositeIndex1,idx01    PRIMARY 4   T2.col1 1   100 Using where
  282.        
  283. SIN(PI() * T3.latitude / 180)
  284. COS(PI() * table3.longitude / 180 - PI() * 37.1092162 / 180)
  285. COS(PI() * T3.latitude / 180)
  286.        
  287. ( T2.Col4 = 'Y' OR T2.Col5 = 'Y' )
  288.        
  289. alter table table2 add static1 bit default 0;
  290. alter table add index idx_static1(static1);
  291. update table2 t2 set static1=1 where ( T2.Col4 = 'Y' OR T2.Col5 = 'Y' );
  292.  
  293. alter table table3 add static2 float(18,16) default 0;
  294. update table3 set static2=SIN(PI() * T3.latitude / 180) where 1
  295.  
  296. alter table table3 add static3 float(18,16) default 0;
  297. update table3 set static3 = COS(PI() * T3.latitude / 180) * COS(PI() * table3.longitude / 180 - PI() * 37.1092162 / 180)   where 1
  298.        
  299. alter table table1 change col2 col2 enum('A','B','C');
  300.        
  301. alter table table1 change col3 col3 enum('X','Y','X-Y');
  302.        
  303. SELECT STRAIGHT_JOIN
  304.       T1.*,
  305.       T2.*
  306.    FROM
  307.       Table1 AS T1
  308.          JOIN Table2 AS T2 ON T1.Col1 = T2.Col1
  309.          JOIN Table3 as T3 ON T1.Col1 = T3.Col1
  310.  
  311.    WHERE static1=1 AND
  312.           T1.Col2 = 'A'
  313.       AND T1.col3 IN ( 'X', 'X-Y')
  314.       AND T1.Col1 != 1
  315.       AND ACOS(  
  316.                  (
  317.                    $usersLatitude_sin_pi_fract180  * t3.static2
  318.                    + $usersLatitude_cos_pi_fract180  * t3.static3
  319.                  )  
  320.                ) <= 0,00252321929476 -- this's 10/3963.191
  321.       ORDER BY T1.Col6
  322.        
  323. t1.col2 = 'A'
  324.        
  325. SELECT  T1.*, T2.*
  326. FROM Table1 AS T1
  327.          JOIN Table2 AS T2   ON ( T1.Col1 = T2.Col1   )
  328.          JOIN Table3 as T3 ON T1.Col1 = T3.Col1
  329.    WHERE  
  330.          (  T1.Col2 =    'A'   collate utf8_bin  AND T1.col3 IN  ( 'X' collate utf8_bin , 'X-Y'  collate utf8_bin )   AND T1.Col1 != 1 )
  331. and T2.static1=1
  332.       AND ACOS(  (   2.3  * T3.static2  + 1.2 * T3.static3  ) ) <= 0.00252321929476
  333.       ORDER BY T1.Col6
  334.        
  335. +----+-------------+-------+--------+-----------------------------------+-----------------+---------+----------------+------+----------+-----------------------------+
  336. | id | select_type | table | type   | possible_keys                     | key             | key_len | ref            | rows | filtered | Extra                       |
  337. +----+-------------+-------+--------+-----------------------------------+-----------------+---------+----------------+------+----------+-----------------------------+
  338. |  1 | SIMPLE      | T1    | ref    | PRIMARY,col2,col3,CompositeIndex1 | CompositeIndex1 | 1       | const          |    1 |   100.00 | Using where; Using filesort |
  339. |  1 | SIMPLE      | T2    | eq_ref | PRIMARY,CompositeIndex1           | PRIMARY         | 4       | testdb.T1.col1 |    1 |   100.00 | Using where                 |
  340. |  1 | SIMPLE      | T3    | eq_ref | PRIMARY                           | PRIMARY         | 4       | testdb.T1.col1 |    1 |   100.00 | Using where                 |
  341. +----+-------------+-------+--------+-----------------------------------+-----------------+---------+----------------+------+----------+-----------------------------+
  342.        
  343. effort = num_rows*key_size/index_cardinality
  344.        
  345. FROM table3
  346. ...
  347. WHERE (t3.latitude-0.15) < $usersLatitude AND  $usersLatitude < t3.latitude+0.15  
  348. AND t3.longitue - 0.15 < $usersLongitude AND   $usersLongitude < t3.longitue + 0.15
  349.        
  350. FROM table3 t3
  351. JOIN table3 t3bis ON t3.id=t3bis.id
  352. ...
  353. WHERE (t3.latitude-0.15) < $usersLatitude AND  $usersLatitude < t3.latitude+0.15  
  354. AND t3.longitue - 0.15 < $usersLongitude AND   $usersLongitude < t3.longitue + 0.15
  355. AND
  356. 3963.191
  357. * ACOS(  (SIN(PI() * $usersLatitude / 180) * SIN(PI() * t3bis.latitude / 180))
  358. + (  COS(PI() * $usersLatitude / 180) * COS(PI() * t3bis.latitude / 180)
  359. * COS(PI() * t3bis.longitude / 180 - PI() * 37.1092162 / 180)
  360. )  
  361. ) <= 10
  362.        
  363. degree * PI / 180 == radians(degree)
  364.        
  365. ...
  366. FROM
  367.     Table1 AS table1 USE INDEX (col6)
  368. LEFT JOIN
  369.     Table2 AS table2  
  370. ...
  371.        
  372. SELECT * FROM
  373. {
  374.     SELECT
  375.         col1, col2, col3, col4, col5, col6
  376.     FROM
  377.         Table1 AS table1
  378.     LEFT JOIN
  379.         Table2 AS table2
  380.     USING
  381.         (col1)
  382.     LEFT JOIN
  383.         Table3 as table3
  384.     USING
  385.         (col1)
  386.     WHERE
  387.         3963.191 *
  388.         ACOS(
  389.         (SIN(PI() * $usersLatitude / 180) * SIN(PI() * table3.latitude / 180))
  390.         +
  391.         (COS(PI() * $usersLatitude / 180) * COS(PI() * table3.latitude / 180) * COS(PI() * table3.longitude / 180 - PI() * 37.1092162 / 180))
  392.         ) <= 10
  393. ) AS sub
  394. WHERE
  395.     col1 != '1'
  396. AND
  397.     col2 LIKE 'A'
  398. AND
  399.     (col3 LIKE 'X' OR col3 LIKE 'X-Y')
  400. AND
  401.     (col4 = 'Y' OR col5 = 'Y')
  402. ORDER BY
  403.     col6 DESC
  404.        
  405. id  select_type table   type    possible_keys   key key_len ref rows    filtered    Extra
  406. 1   PRIMARY <derived2>  ALL NULL    NULL    NULL    NULL    43  100 Using where; Using filesort
  407. 2   DERIVED T3  ALL PRIMARY NULL    NULL    NULL    143153  100 Using where
  408. 2   DERIVED users   eq_ref  PRIMARY,col1,idx01  PRIMARY 4   T3.col1 1   100
  409. 2   DERIVED userProfile eq_ref  PRIMARY,CompositeIndex1 PRIMARY 4   users.col1  1   100
  410.        
  411. ALTER TABLE Table1 ADD INDEX col2_col6_ndx (col2,col6);
  412. SELECT
  413.     table1.*,
  414.     table2.*
  415. FROM
  416.     (
  417.         SELECT * FROM Table1
  418.         WHERE col2='A' AND
  419.         ORDER BY col6 DESC
  420.     ) AS table1
  421. LEFT JOIN
  422.     (
  423.         SELECT * FROM Table2
  424.         WHERE (col4='Y' OR col5='Y')
  425.     ) AS table2
  426. USING
  427.     (col1)
  428. LEFT JOIN
  429.     Table3 as table3
  430. USING
  431.     (col1)
  432. WHERE
  433.     table1.col1 != '1' AND
  434.     table1.col3 IN ('X','X-Y') AND
  435.     3963.191 *
  436.     ACOS(
  437.     (SIN(PI() * $usersLatitude / 180) * SIN(PI() * table3.latitude / 180))
  438.     +
  439.     (COS(PI() * $usersLatitude / 180) * COS(PI() * table3.latitude / 180) * COS(PI() * table3.longitude / 180 - PI() * 37.1092162 / 180))
  440.     ) <= 10
  441. ;
  442.        
  443. ALTER TABLE Table1 ADD INDEX col2613_ndx (col2,col6,col1,col3);
  444. ALTER TABLE Table2 ADD INDEX col4_col1_ndx (col4,col1);
  445. ALTER TABLE Table2 ADD INDEX col5_col1_ndx (col5,col1);
  446. SELECT
  447.     table1.*,
  448.     table2.*
  449. FROM
  450. (
  451.     SELECT table1.col1,table3.latitude,table3.longitude
  452.     FROM
  453.         (
  454.             SELECT col1 FROM Table1 WHERE col2='A' AND
  455.             AND col3 IN ('X','X-Y') ORDER BY col6 DESC
  456.         ) AS table1
  457.     LEFT JOIN
  458.         (
  459.             SELECT col1 FROM Table2 WHERE col4='Y' UNION
  460.             SELECT col1 FROM Table2 WHERE col5='Y'
  461.         ) AS table2
  462.     USING (col1)
  463.     LEFT JOIN Table3 as table3 USING (col1)
  464. ) col1_keys
  465. LEFT JOIN Table1 table1 USING (col1)
  466. LEFT JOIN Table2 table2 USING (col1)
  467. WHERE
  468.     3963.191 *
  469.     ACOS(
  470.     (SIN(PI() * $usersLatitude / 180) * SIN(PI() * col1_keys.latitude / 180))
  471.     +
  472.     (COS(PI() * $usersLatitude / 180) * COS(PI() * col1_keys.latitude / 180)
  473.     * COS(PI() * col1_keys.longitude / 180 - PI() * 37.1092162 / 180))
  474.     ) <= 10
  475. ;