On 9/17/15, Rob Willett <rob.sqlite at robertwillett.com> wrote:
>
> 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 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>?
>
At the end of the CREATE TABLE statement add keywords: "WITHOUT
ROWID". Like this:
CREATE TABLE postcode(
postcode TEXT,
long TEXT,
lat TEXT
) WITHOUT ROWID;
That should do the trick for you. You can search for WITHOUT ROWID in
the search bar on the SQLite homepage for additional information.
--
D. Richard Hipp
drh at sqlite.org
