I'm sorry if this is an obvious question - I'm new to databases. I have
an application where the database is used to log a large number of
simulation events. The database is written once and read many times
(i.e., there are never any inserts or updates after database creation).
The three most interesting tables I have are:
CREATE TABLE events (eid INTEGER PRIMARY KEY, time INTEGER, aid INTEGER,
subtype INTEGER);
CREATE TABLE actions (aid INTEGER PRIMARY KEY, type INTEGER, seqnum
INTEGER, tid INTEGER, instid INTEGER);
CREATE TABLE subtypes (type INTEGER, subtype INTEGER, name TEXT, verbose
INTEGER, PRIMARY KEY(type,subtype) );
The column names are such that columns in different tables with the same
name act as foreign keys. The largest (and most often queried) table is
events, and it can have many millions of entries. The actions table is
also large (about a fifth as big as events) and subtypes is very small
(dozens of entries). My application involves querying events many
times, but very common queries include events that match a particular
verbose value and/or a particular type value. This leads to queries
that have one or two joins, and such queries are substantially slower
than just a query on just the events table.
The question is, what can I do to speed up those queries? The
obvious answer would be to put type and verbose as columns in the events
table, but they would be redundant. Is that par for the course, or is
there some best practice I'm overlooking?
Thanks,
Jeff
_______________________________________________
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
