1

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.

10
  • 2
    This is too general. Please provide the CREATE TABLE statements and the query you are experimenting with. – Laurenz Albe Feb 23 at 16:14
  • I can't share the exact query but basically, two tables share locationid. I've edited the question. Thanks! @LaurenzAlbe – esilik Feb 23 at 16:49
  • 1
    Thanks. And the other alternative? – Laurenz Albe Feb 23 at 17:04
  • 1
    What's the relationship between location and city? I'm confused by this statement: "The idea is that I can create other tables, for each city and I can index again by location." Sample data would be helpful. – bbaird Feb 23 at 17:12
  • 1
    Partitioning your table will not make your RAM grow or your disk go faster. The question is confusing so I have pretty much no idea what you want, but "the first query returns data after ~900ms,subsequent queries takes ~20ms" hints you need more NVME SSDs and more sticks of RAM. – bobflux Feb 24 at 16:06