As has been stated in the past SQLite is a small foot print RDBMS, one
of the things keeping it small is that it does not have a large query
optimizer.
One of the things that can happen is if you have lots of indexes the
optimizer may pick something non optimum
My tricks are as follows:
1. reorder the from tables
2. get the optimizer to ignore indexes that I know cannot help
I do this in the where clause, by adding a zero or concatenating a ""
depending upon the data type
examples:
where sex = "M"
changed to
where sex||"" = "M"
where children = 2
changed to
where children+0 = 2
Steffen Schwigon wrote:
"Christian Schwarz" <[EMAIL PROTECTED]> writes:
Practically one such line for each table and each column.
Why on each column?
I just took the existing DB-import-script from that project. But ...
For example, when your where-clause contains columns A, B and C (in
this order) you should create *one* index on A, B and C.
... you are right, creating a combined index solves the speed problem.
Thanks.
Separate indexes on column A, B and C are not that useful. In this
case, SQLite would most probably use the separate index on column A.
Which would be a pity, wouldn't it? Postgres for instance seems to do
something more clever there, at least it's much faster, even with the
trivial setting of an index on each column.
Anyway, thanks for your answer.
GreetinX
Steffen
