Richard, Thanks for the additional info. I'll look into the multi-column index idea. Sounds as if it might be the solution.
Stephen On Thu, 2007-03-01 at 14:42 +0000, [EMAIL PROTECTED] wrote: > Stephen Toney <[EMAIL PROTECTED]> wrote: > > 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. > > SQLite is limited to a single index per table of the FROM clause. > (In your case the same table occurs twice in the FROM clause, so > each instance can use a separate indices, but each instance can > only use a single index.) Other systems relax this restriction > through the use of bitmap indices. SQLite does not (directly) > support bitmap indices. You can achieve about the same thing > as a bitmap index by playing games with rowids, but the SQL > needed to do so is convoluted. In your case, I think the query > would need to be: > > SELECT count(*) > FROM keyword AS a CROSS JOIN keyword AS b > WHERE a.value='music' > AND b.rowid IN ( > SELECT rowid FROM keyword WHERE value='history' > INTERSECT > SELECT rowid FROM keyword WHERE key=a.key > ); > > It seems so much simpler to use a multi-column index. It is almost > certainly going to be faster. > > > > > 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. > > > > You might get a few hints at http://www.sqlite.org/php2004/page-001.html > and the pages that follow. That is from a talk I gave in 2004. It > is somewhat out of date. My goal for this calendar year is to get > some detailed documentation online about the kinds of issues you > are seeing. > -- > D. Richard Hipp <[EMAIL PROTECTED]> > > > ----------------------------------------------------------------------------- > To unsubscribe, send email to [EMAIL PROTECTED] > ----------------------------------------------------------------------------- -- Stephen Toney Systems Planning [EMAIL PROTECTED] http://www.systemsplanning.com ----------------------------------------------------------------------------- To unsubscribe, send email to [EMAIL PROTECTED] -----------------------------------------------------------------------------
