You will likely be well served by a compound index on (value,key). As the schema stands now, the indexes will help find records with matching values, but not with matching keys; providing one index that correlates the two should help.
Disclaimer: I haven't recreated your schema, added said index and checked that the query plan produced is better. Don't assume this to be good advice without trying it. :) -Tom > -----Original Message----- > From: Stephen Toney [mailto:[EMAIL PROTECTED] > Sent: Thursday, March 01, 2007 7:00 AM > To: [email protected] > Subject: [sqlite] Performance problem > > Dear experts: > > I'm having a performance problem I can't understand. I am running a > "select count(*)" query joining a table on itself, and the query runs > for five minutes using Sqlite3.exe before I get bored and > kill it. This > is on a dual-core box with 4GB of memory, running Windows XP Pro. The > Sqlite version is 3.3.7. > > Here's the problem query with the plan: > > select count(*) from keyword a, keyword b where a.key=b.key and > a.value='music' and b.value='history'; > > 0|0|TABLE keyword AS a WITH INDEX value > 1|1|TABLE keyword AS b WITH INDEX value > > Here's the schema > > CREATE TABLE keyword (key, contyp int, imagecount int, searchcat int, > value, nextword, sec, ipr, fldseq int); > CREATE INDEX key on keyword(key); > CREATE INDEX nextword on keyword(nextword); > CREATE INDEX value on keyword(value); > > The table has 3,486,410 records and the SQLite database totals 320MB. > There are a few small tables in the db besides the KEYWORD table. > > 4,318 records have value='music' and 27,058 have value='history'. The > keys are 12-byte strings. That doesn't seem like an extreme > case to me. > > Using DBI::ODBC::SQLite in a web application the result is just as bad > -- the server times out. > > Any suggestions would be much appreciated! > > > Stephen Toney > Systems Planning > [EMAIL PROTECTED] > http://www.systemsplanning.com > > > -------------------------------------------------------------- > --------------- > To unsubscribe, send email to [EMAIL PROTECTED] > -------------------------------------------------------------- > --------------- > > ----------------------------------------------------------------------------- To unsubscribe, send email to [EMAIL PROTECTED] -----------------------------------------------------------------------------
