MySQL geo search using Python - Tradeoffs and Implementation

Question

I have an sql table that has some data on retail locations with lat, long coordinates. My flask application queries the table to pull the data using python.

table1

id  name   lat       long
1   post   37.788151 -122.407570
2   sutter 37.789551 -122.408302
3   oak    37.815730 -122.288810

I have a point coordinate 37.785980 and -122.410851, and I'd like to find rows from the table that are nearby, say within 0.5 mile radius from the coordinate.

Performance matters. I cannot query all rows due to poor performance, long time sending data to client application and storage / memory issues. What would be a efficient way of doing this?

Using SQL in python, how do I implement an efficient solution that does proximity search and returns rows that meet the distance condition? Here's how I pull data from sql in python.

# mysql connection
import pymysql
from sqlalchemy import create_engine
user = 'user1'
pwd = 'pwd'
host =  'xxxx.1.rds.amazonaws.com'
port = 3306
database = 'db1'
engine = create_engine("mysql+pymysql://{}:{}@{}/{}".format(user,pwd,host,database))



# Readdata
con = engine.connect()

query = '''
        SELECT x
        FROM table1
        GROUP BY key
        '''

df = pd.read_sql(query, con)

con.close()

Happy to share more info if required for context.