CODEWITHSUNDEEP
pythonsqlsqlite
AutomaticStatic
AutomaticStatic

Reputation: 1749

SQLite indexing groups of rows?

I have a large set of city, state pairs I'm loading into a SQLite table. I will be querying the city and will know the state. Suppose I want to look for a particular city that I know is in Texas. The following query is roughly O(n) notwithstanding the limit, right?

SELECT * FROM cities WHERE state_abbr=? LIMIT 1

Is there some way of grouping the rows by state or creating a secondary index or something so that SQLite knows where to the find the 'TX' rows and only search within them? I've considered creating separate tables for each state-- and that's an option-- but I'm hoping I can just do something within this single table to make the queries more efficient.

In the tutorials I've read, the query doesn't change after creating a composite index. Is SQLite just using the index under the hood on the same query?

Upvotes: 0

Views: 298

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1270863

Why not just have a composite index?

create index cities_state_abbr_city on cities(state, city);

Upvotes: 1

Related Questions