6

I have a relatively large SQLite database that I'm building. The general access profile of this database is:

  1. Initial load of 300 million rows.
  2. Initial read of each row, and UPDATE on approximately 30 million rows
  3. 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).

Thanks

  • A separate table sounds a very good idea. The index (on that table) will have every value once, while the existing index has the same values stored multiple times. – ypercubeᵀᴹ Dec 27 '17 at 20:05
  • 2
    But why do you need SELECT DISTINCT if 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. – ypercubeᵀᴹ Dec 27 '17 at 20:20
  • Mostly because I'm a python hacker that uses SQL, not a DBA writing some python. I prefer (and I'll bet is faster) to use assert conditions['Chromosome'] in self._valid_chromosomes versus assert 1 == cur.execute("SELECT 1 FROM dbsnp WHERE chromosome = ? LIMIT 1", [chromosome,]).fetchall()[0][0]. But your point is valid. – A Holman Dec 28 '17 at 16:27
4

I've never used SQLite so bear with me here. But it seems as if this problem is common among many RDBMS platforms.

When you select distinct values from your column you end up scanning all rows in the index:

index scan

This can be a great strategy if there aren't many rows in the table or if the column doesn't have very many duplicate values. But if you have millions of rows for each distinct value then you'll scan millions of rows just to return a single unique value. For data sets like that, it can sometimes be better to get the first distinct value, then skip to the next value, and so on. This can be accomplished via recursion in some platforms. You can also run one query at a time with each getting the next distinct value. For example, you could get the first value with this query:

SELECT MIN(chromosome) FROM dbsnp;

Then get the next value with this query (substituting the filter with the values of the first query):

SELECT chromosome FROM dbsnp WHERE chromosome > 'TEST_1' ORDER BY chromosome LIMIT 1;

And the next:

SELECT chromosome FROM dbsnp WHERE chromosome > 'TEST_2' ORDER BY chromosome LIMIT 1;

And so on. For these queries I'm getting index seeks:

index seeks

For a relatively small data set, the single distinct query takes about 320 ms and the series of LIMIT 1 queries only took 4 ms. You'll of course need to write more code to use this solution, but it might be worth a shot.

db fiddle

  • Nice, I like the method of coming at the problem sideways with recursion. The code to implement that in python is actually not that bad and runs in sub 1s time on my 90M row dataset: chromosome_set = set() current_chromosome = '' while True: db_record = cur.execute("SELECT chromosome FROM dbsnp WHERE chromosome > ? ORDER BY chromosome LIMIT 1", [current_chromosome,]).fetchall() if db_record: current_chromosome = db_record[0][0] chromosome_set.add(current_chromosome) else: break – A Holman Dec 28 '17 at 15:58
  • The python above formatted horribly, but also notice that I'm able to eliminate the SELECT MIN(chromosome) FROM dbsnp; initial query by beginning with a current_chromosome='' as I enter my loop. – A Holman Dec 28 '17 at 16:22
2

In SQLite you can use INDEXED BY my_index to tell the optimizer to use a particular named index (https://www.tutorialspoint.com/sqlite/sqlite_indexed_by.htm).

Try giving this a shot:

SELECT chromosome FROM dbsnp INDEXED BY chromosome GROUP BY chromosome

Edit: This does slow down a lot after 100 million records. After playing around some, you could be better of changing your program to do a quick check on the DB before hand:

db.execute("SELECT COUNT(*) FROM (SELECT 1 FROM dbsnp WHERE chromosome = ? LIMIT 1) sub", input).fetchall()

Your dataset is 1 if it exists or 0 if it doesn't, and it runs fast.

  • Same speed (or slowness). The optimizer seems to be finding the index correctly (see the EXPLAIN QUERY PLAN above), it just seems to be still talking a long time. – A Holman Dec 27 '17 at 18:01
  • I don't see a way for SQLite to read straight from the index. That being said, have you tries just popping off a count(*) after getting their input? SELECT count(*) cnt FROM dbsnp WHERE chromosome = ? Then check if cnt is 0 and throwing an error. You should have to have the index in memory at that point too. – Smörgåsbord Dec 27 '17 at 20:09

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.