That's interesting, I'll give that a shot. I've noticed in my dealings with MS Sql server, that it is very bad at using compound indexes... I hope sqlite is better! :)
Simon Slavin-3 wrote: > > > On 7 May 2010, at 10:47pm, Raeldor wrote: > >> I have 2 individually indexed fields. I have a select statement... >> >> select * from table1 where field1='x' and field2='y' >> >> this takes about 10 sections, yet if I do... >> >> select * from table1 where field1='x' >> >> it's instant, and if i do... >> >> select * from table1 where field2='y' >> >> it's also instant. What's going on here?! > > Your fields are indexed individually. Having used one of the indices to > satisfy one of the requirements, SQLite has to search through the records > it found one by one to find out which fit the other requirement. Make an > index which includes both fields > > CREATE INDEX ON myTable (field1, field2) > > and SQLite will magically figure out that this new index will let it > satisfy both requirements in one search. > > Simon. > _______________________________________________ > sqlite-users mailing list > sqlite-users@sqlite.org > http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users > > -- View this message in context: http://old.nabble.com/Very-Odd...-where-field1%3D%27%27-or-field2%3D%27%27-is-slow-tp28491829p28492610.html Sent from the SQLite mailing list archive at Nabble.com. _______________________________________________ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
