mysql - 使用索引优化sql查询

Question

我有两个 table session 和在线

 |   id   |      time    |
 |    1   |   1413170771 |
 |    2   |   1413174398 |
 |    7   |   1413174567 |

在线是

 |   id   |   username   |   city   |       lat     |     lon     |
 |    1   |     Jon      |  Toronto |   45.4642700  |  9.1895100  |
 |    2   |     Danny    |  Ottawa  |   46.5645600  |  9.3456883  |
 |    7   |     Martin   |  Calgary |   46.6775339  |  9.5469944  |

查询

  SELECT * , ( 6371 * acos( cos( radians( 45.4642700 ) ) * cos( radians( lat ) ) * cos(   radians( lon ) - radians( 9.1895100 ) ) + sin( radians( 45.4642700 ) ) * sin( radians( lat ) ) ) ) AS distance
  FROM online AS o
  INNER JOIN sessions AS s ON o.id = s.id
  HAVING distance <2000
  ORDER BY username DESC 

如何优化此查询？我在 session ID 和在线 ID 上有索引，我可以为临时表添加索引吗？

Answer 1

假设您有下表

 |   id   |      time    |
 |    1   |   1413170771 |
 |    2   |   1413174398 |
 |    7   |   1413174567 |

在线更改为

 |   id   |   username   |   city   |       lat     |     lon     |     dist     |
 |    1   |     Jon      |  Toronto |   45.4642700  |  9.1895100  |   ........   |
 |    2   |     Danny    |  Ottawa  |   46.5645600  |  9.3456883  |   ........   |
 |    7   |     Martin   |  Calgary |   46.6775339  |  9.5469944  |   ........   |

以下查询会发生什么:

SELECT * 
FROM session s, online o
WHERE s.dist < 2000
AND s.id = o.id
ORDER BY username DESC;