Scenario: The data needs to be indexed by location, all of the rows include location info and also city info. I want to index by location, but there are millions of rows. The idea is that I can create other tables, for each city and I can index again by location.
I know that there are options such as Sharding and Partitioning however, I'd like to learn more before going further.
SELECT * FROM t1 INNER JOIN t2 ON t1.locationId = t2.locationId WHERE t1.locationId = '.....';
..~ (1-2K result)
After indexing by locationId (in both two tables), the first query returns data after ~900ms,subsequent queries takes ~20ms. However, since there's a lot of locationId, I think it's not possible to cache all of them, and this will not work.
Any other idea is appreciated.