jeudi 1 janvier 2015

sqlite query does not complete - bad index, or just too much data?

I have two tables in SQLITE: from_original and from_binary. I want to LEFT OUTER JOIN to pull the records in from_original that are not present in from_binary. The problem is that the query I have written does not complete (I terminated it after about 1 hour). Can anyone help me to understand why?


I have an index defined for each of the fields in my query, but explain query plan only mentions one of the indices will be referenced. I am not sure if this is the problem, or if it is just a matter of too much data.


Here is the query I am trying to run:



select * from from_original o
left outer join from_binary b
on o.id = b.id
and o.timestamp = b.timestamp
where b.id is null


The tables each have about 4 million records:


rowcounts


I have an index defined on all the id and timestamp fields (see schema at end of post), but explain query plan only indicates that one of the id indices will be used.


explain query plan


Here is the table schema, including index definitions:


schema


Aucun commentaire:

Enregistrer un commentaire