Stephen Toney <[EMAIL PROTECTED]> wrote:
> 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.
>
The result should be 116,836,444. SQLite has to go retrieve
over 116 million rows from the database in order to compute your
answer. This does seem like it should take a while.
A faster approach would be:
SELECT (SELECT count(*) FROM keyword WHERE value='music')*
(SELECT count(*) FROM keyword WHERE value='history');
Or maybe better yet: Have you looked into using FTS2 for whatever
it is you are trying to do? Full-text search is hard to get right
and you appear to be trying to create your own. Why not use a FTS
subsystem that is already written and testing and available to you?
--
D. Richard Hipp <[EMAIL PROTECTED]>
-----------------------------------------------------------------------------
To unsubscribe, send email to [EMAIL PROTECTED]
-----------------------------------------------------------------------------
