python - Optimize SQLite query with ordering by computed column? -

i using sqlite query to find motor best set of calibration values using formula. in original query ordering computed column (to select top 1), believe slows down query most.

i trying make query fast possible. execution time 0.300 seconds, , able reduce 0.150 seconds restricting table volume between (0.9*1.7006359100341797) , (1.1*1.7006359100341797) , using temptable sorting of computed table (bestmotor).

what else can improve more? motortable table has ~30,000 rows. number of distinct motorvendors 55.

here original query: trying find calx , caly closest specified volume each distinct motorvendor.

select t.motorvendor, ((1/(calx)+1/(caly))) bestmotor ( select motorvendor,        (select calx         motortable t2         motortype = 'text' , t2.motorvendor = tools.motorvendor         order abs(volume - 1.7006359100341797)         limit 1        ) calx,        (select caly         motortable t2         motortype = 'text' , t2.motorvendor = tools.motorvendor         order abs(volume - 1.7006359100341797)         limit 1        ) caly (select distinct motorvendor,       motortable) tools) t       t.calx != '' , t.caly != ''       order bestmotor desc       limit 1; 

and here query using temptable order computed column (with index on computed column in temptable):

delete temptable; insert temptable select t.motorvendor, ((1/(calx)+1/(caly))) bestmotor ( select motorvendor,        (select calx         motortable t2         motortype = 'text' , t2.motorvendor = tools.motorvendor , volume between (0.9*1.7006359100341797) , (1.1*1.7006359100341797)         order abs(volume - 1.7006359100341797)         limit 1        ) calx,        (select calx         motortable t2         motortype = 'text' , t2.motorvendor = tools.motorvendor , volume between (0.9*1.7006359100341797) , (1.1*1.7006359100341797)         order abs(volume - 1.7006359100341797)         limit 1        ) calx (select distinct motorvendor,       motortable) tools) t       t.calx != '' , t.caly != ''       order bestmotor desc       limit 1; select motorvendor, bestmotor temptable order bestmotor desc limit 1; 

update...

i able reduce 0.05 seconds... have created table store distinct motorvendor , change main query this:

select t.motorvendor ( select motorvendor,        (select calx         motortable t2         motortype = 'text' , t2.motorvendor = tools.motorvendor , volume between (0.9*1.7006359100341797) , (1.1*1.7006359100341797)         order abs(volume - 1.7006359100341797)         limit 1        ) calx,        (select caly         motortable t3         motortype = 'text' , t3.motorvendor = tools.motorvendor , volume between (0.9*1.7006359100341797) , (1.1*1.7006359100341797)         order abs(volume - 1.7006359100341797)         limit 1         ) caly        tempmotorvendortable tools) t       order ((1/(calx)+1/(caly))) desc limit 1; 

here schemas of 2 tables , indexes have created.

create table motortable (     caly    real,     calx    real,     volume  real,     motortype   text,     motorvendor text ); create table tempmotorvendortable (     motorvendor text ); create index `motorvendorindex` on motortable (`motorvendor` asc) create index volumeindex on motortable (volume desc) 

output of explain query plan

"0" "0" "0" "scan table tempmotorvendortable tools" "0" "1" "1" "search table motortable using automatic covering index (motorvendor=?)" "0" "0" "0" "execute correlated scalar subquery 1" "1" "0" "0" "search table motortable t2 using index comptoolindex (motorvendor=?)" "1" "0" "0" "use temp b-tree order by" "0" "0" "0" "execute correlated scalar subquery 2" "2" "0" "0" "search table motortable t3 using index comptoolindex (motorvendor=?)" "2" "0" "0" "use temp b-tree order by" "0" "0" "0" "use temp b-tree order by" 

order abs(volume - 1.234) cannot optimized index. (not in next version of sqlite, have expression indexes, because 1.234 value not constant.)

however, don't want order all rows, want 1 nearest row. can done different query, searches first row below value , first row above value (these searches , sortings on volume can use index). take nearest of these 2 rows (sorting 2 rows fast):

(select calx  (select calx, diff        (-- largest value @ or below 1.234              select calx, abs(volume - 1.234) diff              motortable t2              motortype = 'text'                , motorvendor = tools.motorvendor                 , volume <= 1.234              order volume desc              limit 1)        union        select calx, diff        (-- smallest value above 1.234              select calx, abs(volume - 1.234) diff              motortable t2              motortype = 'text'                , motorvendor = tools.motorvendor                 , volume > 1.234              order volume asc              limit 1)        order diff        limit 1) ) 

to make searches in motortable efficient, need single index on 3 columns, , inequality comparison must on last one:

create index xxx on motortable(motortype, motorvendor, volume);