Re: [sqlite] How can i see if an query uses an index ?

[Dennis Cote]

Jon Friis wrote:

Hi all
I have the following table + index

CREATE TABLE O_YDLRK_CK91_HIST
(
   ISIN          TEXT          NULL,
   BOERS_DATO    DATE      NOT NULL,
   TERM_DATO     DATE      NOT NULL,
   AFDRAG_BELOEB REAL         NULL,
   RENTE_BELOEB  REAL         NULL,
   CONSTRAINT XPKO_YDLRK_CK91_HIST
   PRIMARY KEY  (ISIN,BOERS_DATO,TERM_DATO)
);
CREATE INDEX XIE1O_YDLRK_CK91_HIST
   ON O_YDLRK_CK91_HIST(BOERS_DATO);

with aprx. 5.000.000 rows.

I do the following query against the table

Select a.Boers_Dato,a.Isin,a.Term_dato,a.Afdrag_Beloeb,a.Rente_Beloeb 
from   O_YDLRK_CK91_HIST a,(
	select Isin as FK,Term_dato as tm, max(Boers_Dato) as p 
       from O_YDLRK_CK91_HIST 
	where Isin='DK0009253064' and Boers_Dato<='2004-10-01' 
	group by Isin,Term_dato) b 
where a.Isin=b.FK and a.Term_dato=b.tm 
and a.Boers_Dato=b.p 
and a.Term_dato>='2004-10-01' 
Order by a.Isin, a.Term_dato, a.Boers_Dato 

And have some problems with execution times. This takes aprx. 5 minutes to get 
an answer - which is longer than i have expected (compared to other 
databases)

So how can i see if it really uses the index or not ???

Any other performance enhancement / efficient rewriting tips are appreciated - 
since i am a newbie to SQLite.

Thanx in advance
Jon Friis

 

Jon,

You can use the "explain query plan" command to display the tables and 
indexes that are used to implement a query. Try this:

explain query plan Select a.Boers_Dato,a.Isin,a.Term_dato,a.Afdrag_Beloeb,a.Rente_Beloeb 
from   O_YDLRK_CK91_HIST a,(
	select Isin as FK,Term_dato as tm, max(Boers_Dato) as p 
       from O_YDLRK_CK91_HIST 
	where Isin='DK0009253064' and Boers_Dato<='2004-10-01' 
	group by Isin,Term_dato) b 
where a.Isin=b.FK and a.Term_dato=b.tm 
and a.Boers_Dato=b.p 
and a.Term_dato>='2004-10-01' 
Order by a.Isin, a.Term_dato, a.Boers_Dato

HTH
Dennis Cote