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] -----------------------------------------------------------------------------