Hi
I have a relatively simple DB with sqlite 3.5.6
The following query runs in around 10 secs with no indexes, however if
I index the start field (datetime) it takes around 10 minutes +
Select guide.id,channels.Channel,start,start,name,stop,stop,cat0 from
guide join channels on pnr=ind left join blacklist on
blacklist.channel =channels.Channel where (start >=
datetime('2009-01-16 02:05:42','-1 hours') or (start < '2009-01-16
02:05:42' and stop > '2009-01-16 02:05:42')) and blacklist.channel
is null and start <'2009-01-17 02:05' order by
start,upper(channels.channel) ;
output of sqlite_master
table|dump|dump|2|CREATE TABLE dump
(uniq_id,id,tsid,pnr,start,stop,cat0 ,lang,name ,stext,tv )
table|blacklist|blacklist|3|CREATE TABLE blacklist (id INTEGER PRIMARY
KEY,channel )
table|guide|guide|4|CREATE TABLE guide (id
,event_id,tsid,pnr,start,stop,cat0 ,lang,name ,stext,tv )
table|archive|archive|5|CREATE TABLE archive (id
,event_id,tsid,pnr,start,stop,cat0 ,lang,name ,stext,tv )
table|recordings|recordings|6|CREATE TABLE recordings
(prog_id,channel,program,start,end,at_id,tsid, ffmpeg_pid, col
atend_id, atend_id)
table|config|config|7|CREATE TABLE config (key,value)
table|startsel|startsel|8|CREATE TABLE startsel (start,startorig)
table|addtime|addtime|9|CREATE TABLE addtime (start)
table|category|category|10|CREATE TABLE category (cat)
table|channels|channels|11|CREATE TABLE channels (ind,Channel,tsid,vpid,apid )
index|archind|archive|142577|CREATE INDEX archind on archive(id)
index|arch_start|archive|150019|CREATE INDEX arch_start on archive(start)
index|guide_start|guide|157390|CREATE INDEX guide_start on guide(start)
obviously this is the opposite to which I would expect
any ideas?
_______________________________________________
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
