CODEWITHSUNDEEP
sqlsqlite
Robert
Robert

Reputation: 38213

Will splitting data into several SQLite tables or databases be faster for retrieving data

I am creating a SQLite database for location data. There about 10 million rows and I want to get the SELECT statements as fast as possible.

I will only have to publish the database once and I dont care how long the creation phase will take.

The user will only be looking at one part of a map at once, therefore the data that geographically is close to each other needs to be accessed at the same time.

I have split my data into several tiles. Each tile corresponds to a region of latitude & longitude based on open street map tiles

My questions is, for the fastest lookup time, should I:

  1. Have several databases, one databases per tile, OR
  2. Have one database, containing several tables, one table per tile, OR
  3. Have one big database with just one table, because splitting the data will not make search any faster.

I could just try each way, but I would really like to understand the solution.

Upvotes: 1

Views: 1245

Answers (1)

CL.
CL.

Reputation: 180182

If you have several databases or several tables, your code has to find out which one to search before doing the actual search query. Furthermore, if the search window overlaps two tiles, you have to do two searches and merge the results. Additionally, splitting doesn't help you with the search inside one tile.

To do geographic searches, you should use a two-dimensional index like an R-tree. When you are doing that, searches are automatically restricted to the appropriate parts of the data, so using one table is both simplest and fastest.

Upvotes: 3

Related Questions