Hello, % sqlite3 -version 3.5.9
I'm trying to figure out a frugal way to handle a unique key constrain... I tried using both 'insert or ignore' and a self join. The self join seems to be noticeably faster even though 'insert or ignore' would empirically appear to be the better deal (shorter query plan, less VM instructions). Specifically, given the following DML: insert or ignore into token( name ) select stage.token as name from stage order by stage.token; One gets a query plan like such: 0|0|TABLE stage And 'explain' reports 58 VM instructions. On the other hand, the following self join... insert into token( name ) select stage.token as name from stage left join token on token.name = stage.token where token.id is null order by stage.token; ... uses a query plan like such: 0|0|TABLE stage 1|1|TABLE token WITH INDEX token_name ... and 82 VM instructions. Nonetheless, the self join would appear to be around 10% faster than the 'insert or ignore' flavor. Not sure why this is the case though... considering the apparent overhead incurred by the join. Thoughts? -- PA. http://alt.textdrive.com/nanoki/ _______________________________________________ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
