On 29 Oct 2010, at 5:07pm, Jonathan Haws wrote: > SELECT dted_alt FROM Dted WHERE (dted_lat=%d AND dted_lon=%d); > > The numbers fed to dted_lat and dted_lon are typically on the order of > 370000 and -1110000. > > What can we do to speed up our SELECT statements? Minutes is > unacceptable for our application. We were hoping we could run somewhere > on the order of 500 queries per second and get valid results back. > > I am not an SQL expert, but I was reading about indexes that that it is > best to have a specific index per SELECT. Since we only have one, > this is the index I am creating now (it has been creating this index on my > machine for the past 10 minutes now): > > CREATE INDEX idx_ll ON Dted(dted_lat,dted_lon);
Yes, that's a good index for that particular SELECT. It will make that SELECT return results in tiny fractions of a second. And with an 8.7 Gig database it will take some time to create the index: perhaps even hours, depending on your platform. Don't worry about it: you seem to be doing the right thing. Just let it run. Simon. _______________________________________________ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
