[email protected] wrote: > Is there any performance gain or loss from stuffing two columns into one > index, e.x. > > CREATE INDEX `myindex1` ON `nodes` (`n_lat`, `n_lon`); > > compared to: > > CREATE INDEX `myindex2` ON `nodes` (`n_lat`); > CREATE INDEX `myindex3` ON `nodes` (`n_lon`); > > when doing a query like this: > > SELECT * FROM nodes > WHERE n_lat >= 51 AND n_lon >= -1 AND n_lat <= 53 AND n_lon <= 1;
No difference. Only one index can be used per table, so having two of them is pointless. However, two inequality checks cannot be satisfied from a single index, so myindex1 would only help with conditions on n_lat anyway, and so work no better than myindex2 (while taking up more space). > I have been trying to get R*-Trees working and I have had limited success > with my roads database but nodes do not want to cooperate, getting > Constraint Errors all around. R-trees were designed for problems just like yours. Personally, I'd try to get one working. Ask here - there are people knowledgeable about R-trees on this list (unfortunately, I'm not one of them). > Also, is a UNIQUE index faster to read than a non-UNIQUE index in any way? Not to my knowledge. Igor Tandetnik _______________________________________________ sqlite-users mailing list [email protected] http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
