I may be wrong on this, but try this: reverse the order of the tables in the FROM clause. you should not need the extra index, if the joins is FROM functions TO symbols event better, use JOIN syntax: select * from functions join symbols on functions.symbolID=symbols.id
this should only need the primary keys which you already have > -----Original Message----- > From: Jim Crafton [mailto:[EMAIL PROTECTED] > Sent: Friday, January 27, 2006 5:30 AM > To: sqlite-users@sqlite.org > Subject: [sqlite] sqlite problem > > I'm a newbie to using SQL in general, so my apologies if this > has been answered before. > > I have a series of tables I'd like to create to represents > the output that comes from parsing a bunch of source code > files. Basically ctags output in DB format. > > I've created the following tables using sqlite3 > > CREATE TABLE Types ( > TypeID INTEGER PRIMARY KEY > , TypeInfo CHAR(125) > ); > > CREATE TABLE Templates ( > TemplateID INTEGER PRIMARY KEY > , TemplateSignature CHAR(125) > ); > > CREATE TABLE Files ( > FileID INTEGER PRIMARY KEY > , Path CHAR(255) > ); > > CREATE TABLE Symbols ( > Id INTEGER PRIMARY KEY > , Kind INTEGER > , Name CHAR(125) > , FileID INTEGER > , TypeID INTEGER > , Offset INTEGER > , LineNumber INTEGER > , TemplateID INTEGER DEFAULT 0 NOT NULL > , Parent INTEGER DEFAULT 0 > ); > > CREATE TABLE Functions ( > FuncID INTEGER PRIMARY KEY > , Signature CHAR(125) > , SymbolID INTEGER > , ReturnTypeID INTEGER NOT NULL > ); > > > I can then populate the tables fine. In my test, the symbols > table had over 11,000 entries and the functions table over > 7,200 entries. > > When I run the following query: > "select symbols.name, functions.signature from symbols, > functions where functions.symbolid = symbols.id;" > > This takes a long time (over 20 secs) on a P4 3 Ghz with 1 Gb RAM. > Should it take this long? Is it slow because my table is > setup incorrectly? > > Thanks so much! > > Jim Crafton > > >
