I have a python script that works by first creating some tables in a SQLite3 DB from lots of text files, then creating some views by joining the tables, and finally querying one of the views and performing some analysis on the results. The largest table contains ~2 billion rows, while the others contain a few million rows.
Running simple queries on the final view is very slow (~4 hours per query), so I want to create an index to allow faster queries. Since indexing on views is impossible, I am trying to find the right indexes to create on the underlying tables. Here is what I did:
I start with 4 tables:
sqlite> SELECT COUNT(*) FROM blast; 1930113087 sqlite> SELECT COUNT(*) FROM orthologs; 16906170 sqlite> SELECT COUNT(*) FROM seqnames; 982237 sqlite> SELECT COUNT(*) FROM genomenames; 22
And then I create views...
CREATE VIEW blast_seqnames AS SELECT b.*, s.* FROM ( SELECT b.*, s.* FROM blast b INNER JOIN ( SELECT seqid AS qseqid, seqname AS qseqname FROM seqnames ) s ON b.qseqid = s.qseqid ) b INNER JOIN ( SELECT seqid AS sseqid, seqname AS sseqname FROM seqnames ) s ON b.sseqid = s.sseqid;
CREATE VIEW blast_seqnames_genomenames AS SELECT b.*, s.* FROM ( SELECT b.*, s.* FROM blast_seqnames b INNER JOIN ( SELECT genomeid AS qgenomeid, genomename AS qgenomename FROM genomenames ) s ON b.qgenomeid = s.qgenomeid ) b INNER JOIN ( SELECT genomeid AS sgenomeid, genomename AS sgenomename FROM genomenames ) s ON b.sgenomeid = s.sgenomeid;
CREATE VIEW blast_orthologs AS SELECT b.*, o.* FROM blast_seqnames_genomenames b INNER JOIN orthologs o ON b.qgenomename LIKE o.prot1_genome || '%' AND b.qseqname = o.prot1 AND b.sgenomename LIKE o.prot2_genome || '%' AND b.sseqname = o.prot2;
CREATE VIEW blast_orthologs_max_weight AS SELECT *, max(bitscore) AS max_weight FROM blast_orthologs GROUP BY qseqid, sseqid;
CREATE VIEW blast_orthologs_bidirect AS SELECT orthogroup, prot1, prot2, prot1_genome, prot2_genome, AVG(max_weight) OVER( PARTITION BY MIN(prot1, prot2), MAX(prot1, prot2), MIN(prot1_genome, prot2_genome), MAX(prot1_genome, prot2_genome) ) WEIGHT FROM blast_orthologs_max_weight;
Now I want to query the view
blast_orthologs_bidirect view with something like:
SELECT * FROM blast_orthologs_bidirect WHERE orthogroup = 'OG0005821';
Since this is very slow, I created an index on the field by which I'm querying:
CREATE INDEX orthogroup ON orthologs (orthogroup);
But this doesn't seem to improve the situation, so I assume I chose the wrong index.
- So what would be a better choice?
- Or maybe this is a lost cause and SQLite just can't handle such large tables?
- Will something like MySQL perform better?