I want to know SQLite join algorithm
On 12/04/07, P Kishor <[EMAIL PROTECTED]> wrote:
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:
This Query is not equal to my query. 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] -----------------------------------------------------------------------------
-- Cesar Rodas http://www.cesarodas.com/ Mobile Phone: 595 961 974165 Phone: 595 21 645590 [EMAIL PROTECTED] [EMAIL PROTECTED]
