On Fri, May 23, 2008 at 12:01 PM, Jeff Gibson <[EMAIL PROTECTED]> wrote: > 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?
I'm new to databases too. But since no one has answered I'll take a stab at it: make an index for each table on the column that you join. That should speed up the joins. But I don't think it will be as fast as putting everything in one table and making an index on columns you do WHERE on. _______________________________________________ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
