Re: [sqlite] SQLite join-mechanisms question

[drh]

"Cesar Rodas" <[EMAIL PROTECTED]> wrote:
> Hello.
> 
> I have a question about SQLite join-mechanisms. Let me explain with an
> example.
> 
> I have the follow table.
> CREATE TABLE a(
>    word_id INTEGER,
>    doc_id INTEGER
> );
> 
> CREATE INDEX "a_index1" ON "a"(
> "doc_id"  ASC
> );
> 
> CREATE INDEX "a_index" ON a (
>    "word_id" DESC
> );
> 
> And how can SQLite do an optimized join of the follow query
> SELECT
>     a.doc_id
> FROM
>     a, a as a1, a as a2
> WHERE
>     a.doc_id = a1.doc_id and a1.doc_id = a2.doc_id  and
>     a.word_id = 1 and a1.word_id = 2 and a2.word_id = 4
> LIMIT 0,20
> 

My (untested) guess it that you will get better performance
if you do this:

   DROP INDEX a_index1;
   DROP INDEX a_index;
   CREATE INDEX a_index2 ON a(doc_id, word_id);

I will also guess that ANALYZE will help in this case.

--
D. Richard Hipp <[EMAIL PROTECTED]>


-----------------------------------------------------------------------------
To unsubscribe, send email to [EMAIL PROTECTED]
-----------------------------------------------------------------------------