Hi Nate,
Are you always using a LIKE pattern that starts with a fixed string,
or is the wildcard sometimes first? If it's always fixed, your query
should probably be very fast if you are searching on a properly
indexed field. Check out http://www.sqlite.org/optoverview.html under
"4.0 The LIKE optimization" and see if there is a way to get your
query to fit that model: name >= 'foo' and name < 'fop'.
Well, the example is artificial. I wanted to show an efficient
query that would result in potentially many rows. Therefore
I showed in my example the creation of an index of the field
I was searching with like. I am very aware of the like
optimization :-)
This work fine currently. The question is, is this an artifact
of the current implementation or can I rely on this in the
future? (I know that's not compatible with the SQL specs)
I can't speak to future development, but from my recent looking at the
code involved I think it would be hard to have the rows inserted in
any order than that of the index that is used. So for an embedded app
where you can test before any new versions, I wouldn't worry too much
about it changing.
Yea, that was what I expected.... However, a really clever optimizer
could optimize away the ORDER clause in my query, because tables are
not ordered in SQL:
CREATE TEMP TABLE view1 AS
SELECT oid AS dataid
FROM data
WHERE name like 'foo%' ORDER number;
So, my little trick relies on sqlite not optimizing away the
ORDER clause.
> On the other hand, I would try hard to come up
> with a fast version that doesn't need to rely on a temp table.
There is another advantage of the temp table: when a 'cold' database
is opened (I mean nothing is in the OS file cache), then queries
that selectively access 'random' rows are pretty slow. My application
gets a list of items from outside the database. Randomly accessing
those item it is very slow. However, when a query has run over the
table the queries are much faster afterwards... OK I'm faking that
my application needs less memory (the footprint seems pretty good,
however, the hidden footprint is the OS file system cache...).
What I safe is the transfer of the data to memory of my application.
That would require twice the memory: the file system cache plus
the used memory.
Michael
