jeudi 28 avril 2016

What is the most efficient strategy for lookups on a large, static table which is already in sorted order (sqlite)?

I have a basic reverse lookup table in which the ids are already sorted in ascending numerical order:

id INT NOT NULL,
value INT NOT NULL

The ids are not unique; each id has from 5 to 25,000 associated values. Each id is independent, i.e., no relationships between the ids.

The table is static. Read only, no inserts or updates ever. The table has 100-200 million records. The database itself will be around 7-12gb. Sqlite.

I will do frequent lookups in this table and want the fastest response time for each query. Lookups are one-direction only, unordered, and always of the form:

SELECT value WHERE id IN (x,y,z)

What advantages does the pre-sorted order give me in terms of database efficiency? What should I do differently than I would with typical unordered tables? How do I tell sql that it's an ordered list?

What about indices: is it necessary or even helpful to create an index on id?

Aucun commentaire:

Enregistrer un commentaire