'optimize sql query with index

I' have two tables session and online

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

online is

 |   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  |

the query

  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

How I can optimize this query? I have indexes on session id and online id can i add index for temporary table?

mysqlsql


Solution 1:[1]

Assume you have following tables

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

online CHANGED TO

 |   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  |   ........   |

what with following query:

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

Sources

This article follows the attribution requirements of Stack Overflow and is licensed under CC BY-SA 3.0.

Source: Stack Overflow

Solution Source
Solution 1

Related Questions