Use a spacial index to speed up the queries

[johrstrom]

• r-tree is supported by sqlite3
• geohash is another option

This should be done after switching to sqlite3 from mysql (and then from there, optionally another strategy as necessary).

sqlite3 can be built in the Docker container to include the R-tree index feature https://sqlite.org/rtree.html

When the search involves location data, this matters. Using an R-tree index, in a database with 3 million records only around 50 comparisons to longitude/latitude are required. Without a spacial index like R-tree, a b-tree index on individual longitude/latitude columns are used, resulting in many more comparisons.

There is also https://www.gaia-gis.it/fossil/libspatialite/index which may provide a more user friendly interface, though my guess is the rtree extension built into sqlite3 is good enough (and easy to compile from source in the Dockerfile).

Having the pipeline and the web app run in the same Docker image means you can really use any tool you want.