Re: [sqlite] SQLite join-mechanisms question

[P Kishor]

On 4/12/07, 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

Cesar,

Am I flaking out or are you just making things more complicated for
yourself than they need be --

What the example do is find the doc_id that has word_id 1,2 and 4.
And suppose that there is:

Would a simple statement like the following work?

SELECT doc_id
FROM a
WHERE word_id IN (1, 2, 4)

   - 40000 docs with word_id 1
   - 50000 docs with word_id 2
   - 353500 docs with word_id 4

--
Puneet Kishor http://punkish.eidesis.org/
Nelson Inst. for Env. Studies, UW-Madison http://www.nelson.wisc.edu/
Open Source Geospatial Foundation http://www.osgeo.org/education/
---------------------------------------------------------------------
collaborate, communicate, compete
=====================================================================

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