Yeah, I am letting it run and will check to see how things work once it is finished. I expect things to speed up quite a bit.
-- Jonathan R. Haws Electrical Engineer Space Dynamics Laboratory (435) 797-4629 jh...@sdl.usu.edu ________________________________________ From: sqlite-users-boun...@sqlite.org [sqlite-users-boun...@sqlite.org] on behalf of Simon Slavin [slav...@bigfraud.org] Sent: Friday, October 29, 2010 10:14 AM To: General Discussion of SQLite Database Subject: Re: [sqlite] Slow SELECT Statements in Large Database file 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 _______________________________________________ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
