Thanks, Igor, Richard, and Tom, Why doesn't SQLite use the index on key? I can see from the plan that it doesn't, but why not? Can only one index be used per query?
This seems strange. I have used SQL Server and Visual Foxpro for this same problem, and they both handle this query in a second if the indexes are there. Is there a good place to read more about this SQLite behavior? I'm fairly familiar with the online documentation and don't recall reading this. Thanks a million! Stephen On Thu, 2007-03-01 at 07:54 -0500, Tom Briggs wrote: > 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: sqlite-users@sqlite.org > > 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] > > -------------------------------------------------------------- > > --------------- > > > > -- Stephen Toney Systems Planning [EMAIL PROTECTED] http://www.systemsplanning.com ----------------------------------------------------------------------------- To unsubscribe, send email to [EMAIL PROTECTED] -----------------------------------------------------------------------------
