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