I don't claim any SQLite expertise, but indexes on values like
True/False, Male/Female -- i.e. where there are only a couple or a few
different values possible in the column -- are normally to be avoided
because low-cardinality indexes tend to be inefficient. What is the
advice of the SQLite gurus in this respect?
Regards
Tim Romano
P.S. I've noticed a striking performance boost in SQLite when joined
inline views (as shown below) are used instead of standard table joins.
I haven't delved into it but it could be that because the transient
table instantiated by the inline select contains only a few rows and
SQLite uses this set of rows as the "inner loop", this turns out be a
better approach for the query than the indexes that might have been
chosen otherwise.
select a, b, c
from myTable T1
JOIN
(select x, y, z from myOtherTable where ...) as T2
on T1.a = T2.z
On 1/6/2010 5:28 PM, Doyel5 wrote:
> The table ‘Occurrence’ has 1,600,000 records. The table looks like:
> CREATE TABLE Occurrence
> (
> SimulationID INTEGER, SimRunID INTEGER, OccurrenceID INTEGER,
> OccurrenceTypeID INTEGER, Period INTEGER, HasSucceeded BOOL,
> PRIMARY KEY (SimulationID, SimRunID, OccurrenceID)
> )
>
>
> CREATE INDEX "Occurrence_HasSucceeded_idx" ON "Occurrence" ("HasSucceeded"
> ASC)
>
>
> <snip>
_______________________________________________
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
