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