Well, this may be a heretical answer. Given what you have said, I wouldn't
even try to use SQLite. Well, at least not directly. I would use a "hash
table". SQLite's indexing, if I understand correctly, is a B-Tree. And that
is the _only_ option. What would likely work better is a "hash index". You
might get some more concrete answers if you were to post the OS and
implementation language. Such as, Windows 8.1 using C#. Or Linux 64-bit
using C++. Also, do you an SQL data base? Perhaps something else would be
better, if you're not really doing relational queries. But I don't know
what, given that I don't know your system environment.
On Thu, Sep 17, 2015 at 6:58 AM, Rob Willett <rob.sqlite at robertwillett.com>
wrote:
> Hi,
>
> There was a topic on here a few weeks ago which I cannot remember and
> cannot find in the mess that is my e-mail system and after spending the
> last hours search the SQLite archives I still can?t find it so will ask
> here if anybody can remember or help. I can?t even format the question for
> Google to search on :(
>
> The specific question I have is about trying to provide the fastest
> response possible to a select query. I recall that the e-mail talked about
> using an index to satisfy the query and therefore never having to go out to
> get the rest of the data from the table, so it was a lot quicker. Is there
> anything that I need to do specially to make this happen. e.g. if I put all
> the fields of the table in the index BUT I really only search on the
> primary key
>
> The reason for this I want to look up UK postcodes (Zip codes to our
> American brethren) and get their longitude and latitude. A UK postcode
> identifies a number of houses or commercial buildings. Depending on the
> area it can be just one building (a big one) or if you are in the country
> it can be quite a big area. If you sent a letter just to a postcode with no
> other identifier it probably wouldn?t get delivered, but putting a name on
> it or a building number, there?s a very good chance the post(wo)?man will
> deliver it.
>
> The CSV file looks like this
>
> id,postcode,latitude,longitude
> 1,AB101XG,57.144165160000000,-2.114847768000000
> 2,AB106RN,57.137879760000000,-2.121486688000000
> 3,AB107JB,57.124273770000000,-2.127189644000000
> 4,AB115QN,57.142701090000000,-2.093014619000000
> 5,AB116UL,57.137546630000000,-2.112695886000000
> ?.
> Couple of million more lines
>
> The entire database schema looks like this. I know its complicated but
> bear with me :)
>
> CREATE TABLE "postcode" (
> "postcode" text NOT NULL,
> "long" TEXT NOT NULL,
> "lat" TEXT NOT NULL,
> PRIMARY KEY("postcode")
> );
>
> The only query that will ever run will be
>
> select long,lat from postcode where postcode = ?<some string>?
>
> Note I drop off the id field (column 0 in the CSV file) as its of no
> interest to me. I also store the long and lat as strings as I don?t want
> any number formatting changes at all. Rounding on a GPS number could cause
> the wrong location to be used.
>
> The database will do nothing but return long and lat based on doing a
> postcode lookup. There will never be any updates or changes. If there are,
> the whole database will be regenerated.
>
> I need this to be as fast as possible and if necessary I?ll put it all in
> RAM. The database is currently 120MB so it would easily fit in RAM. As it
> never changes (perhaps 4 times per year), it could stay there.
>
> Is there anything else from the database schema side that would make
> things quicker? e.g. If I created an index with postcode, long, lat in,
> would that be quicker? or if i changed the long, lat to real (though I?m
> reluctant to do so), would that make a lot of difference?
>
> Any suggestions gratefully received and apologies for not being able to
> find it in the archives.
>
> Thanks,
>
> Rob
> _______________________________________________
> sqlite-users mailing list
> sqlite-users at mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>
--
Schrodinger's backup: The condition of any backup is unknown until a
restore is attempted.
Yoda of Borg, we are. Futile, resistance is, yes. Assimilated, you will be.
He's about as useful as a wax frying pan.
10 to the 12th power microphones = 1 Megaphone
Maranatha! <><
John McKown
