sql - Performance selecting rows not matching entry in another table when NULL is present -

related question: how select rows no matching entry in table?

i trying select rows using method , couldn't work in sqlite. after bit of wrangling occurred me reason might there null values in fields. sure enough, right, , when changed = is in query below things started behaving expected:

create temp table newevent(id integer,t integer,name,extra,extra2,extra3); insert newevent(id,t,name,extra,extra2,extra3) values                            (0, 1376351146, 'test', null, null, null),                            (0, 1376348867, 'old', null, null,null); select n.id,n.t,n.name,n.extra,n.extra2,n.extra3 newevent n         left join event e on n.t = e.t , n.name e.name                                       , n.extra e.extra;                                       , n.extra2 e.extra2;                                       , n.extra3 e.extra3        e.id null; drop table newevent; 

in above example, there existing record in table event name='old'. newevent table defined same original event table.

however, noticed big problem: query taking 30 seconds run! if change only n.name e.name n.name = e.name leave other ises as-is, query takes around 400ms. (there around 3 million records in table event.)

why big difference in performance? turns out can use = instead of is name comparison because never null, if ever null seems break. conversely, concerned @ point query might start running slow, since don't understand name makes equality query run faster. guess maybe sqlite somehow knows there nulls in fields , able optimize bit more firm wild guess.

as far can tell, is = additional provisio treat null comparisions same if empty strings (assuming there no actual empty strings compare). why using = on name field 75 times faster, has no effect on performance on fields???

in join, sqlite can optimize = index lookups, not is. furthermore, not possible use more 1 index per table in single query.

so either not have multi-column index includes both name , extra*, or selectivity of additional column(s) not high enough matter.

you try different query using compound select:

select t, name, extra, extra2, extra3 newevent except select t, name, extra, extra2, extra3 event 

however, not allow column not compard (like id).