1

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...

blast_seqnames

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;

blast_seqnames_genomenames

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;

blast_orthologs

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;

blast_orthologs_max_weight

CREATE VIEW blast_orthologs_max_weight AS
SELECT *, max(bitscore) AS max_weight
FROM blast_orthologs
GROUP BY qseqid, sseqid;

blast_orthologs_bidirect

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.

Questions

  • 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?
6
  • 1
    What is blast_orthologs_max_weight ? But this does look like a case where your filters against your views can not be adequately pushed to act against your tables because of all the aggregation/analytics that must happen first. i.e. Your views are going to need to be fully computed before you can filter, so indexes are usually not going to be helpful (you'll be reading all the table anyway). The like join predicate is also a worry - is this really needed - can you convert it to some equality condition so that it can be used in a hash join? Jul 2 at 9:38
  • Sorry, forgot to include blast_orthologs_max_weight - fixed now. I guess I can think of some way to convert the like to a =` when creating blast_orthologs, but is this really the issue? What if I create blast_orthologs_bidirect as a tables instead of views and then index it?
    – soungalo
    Jul 2 at 10:16
  • Hi,welcome to dba.se! My (quick) searches revealed that MV aren't available on SQLite or MySQL. You mention changing db - I would strongly urge you to go with PostgreSQL. SQLite mirrors its syntax - it has functionality that MySQL doesn't have that you will spend much time re-implementing. It's support for JSONB is unmatched . FlyBase uses it! I could give you many more reasons - but check my profile - my special interest is genomic dbs... Jul 2 at 11:44
  • It may not be stellar in every department, but you can perform SQL queries mixing structured and semi-structured data (JSONB docments). Its standards compliance is also 2nd to none. Don't necessarily take my word for it - test and benchmark over a range of scenarios - this is where PostgreSQL's general applicability will shine. I would also stress that (published) database benchmarks are a total quagmire - many of those performing them have agendas and/or little knowledge. Caveat Emptor! Jul 2 at 11:50
  • Thanks for the advice @Vérace - I'll check this out.
    – soungalo
    Jul 2 at 12:24

Your Answer

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

Browse other questions tagged or ask your own question.