I have a relatively large SQLite database that I'm building. The general access profile of this database is:
- Initial load of 300 million rows.
- Initial read of each row, and UPDATE on approximately 30 million rows
- Ongoing read-only access through a python script with a MyDatabaseReader class.
The data structure is a flat denormalized table and queries are of the form:
WHERE chromosome=TEXT, position=INT, reference_sequence=TEXT, alternate_sequence=TEXT
I would like to validate that an incoming query requests a valid chromosome name, based on the contents of the database. Requesting chromosome='chr13' is valid, but in the case of '13' or 'chr31' I would like my python script to throw an error, instead of silently returning zero rows. To enable this, my during the init of my python class, it makes an initial query to get the set of valid chromosome names:
SELECT DISTINCT chromosome from dbsnp;
This query takes a LONG time, and bogs down the execution. I have tried both a compound index on (chromosome, position, reference_sequence, alternate_sequence), as well as a single index on chromosome, and have verified with EXPLAIN QUERY PLAN that in both cases the index gets used.
sqlite> EXPLAIN QUERY PLAN SELECT DISTINCT chromosome FROM dbsnp; order|from|detail 0|0|TABLE dbsnp WITH INDEX chromosome ORDER BY
My question: Is there some SQL trick to query directly from the chromosome index. I don't care about anything else in the row, and it seems that the index is a pre-built version of the data that I want to return.
Alternatively, I'm considering constructing a chromosome_names table that I populate with my SELECT DISTINCT query above after database load and update. Because it scares me to construct a static table that can fall out of sync with the main table I'm considering triggers to update the chromosome_names table on change of the main table. However, I'm concerned that this may cause significant churn should I update rows in the main table, and more importantly, that I'm reinventing what is essentially contained in an index.
Is there a good way to get my distinct values query from the index directly, or alternatively, is there a way to have SQLite throw an error should the queried value for chromosome be outside of the set of contained values (note: position, ref, and alt sequences are expected to sometimes query for unexpected values so erroring on zero rows returned will not work).
SELECT DISTINCTif you only want to check whether
'chr31'exists or not?
SELECT 1 FROM dbsnp WHERE chromosome = 'chr31' LIMIT 1;should answer that fast, using the index.
assert conditions['Chromosome'] in self._valid_chromosomesversus
assert 1 == cur.execute("SELECT 1 FROM dbsnp WHERE chromosome = ? LIMIT 1", [chromosome,]).fetchall(). But your point is valid.