On 12/04/07, [EMAIL PROTECTED] <[EMAIL PROTECTED]> wrote:
"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] -----------------------------------------------------------------------------
Thanks for the answer mister Hipp, but I am searching an SQL help. I'd like to know how is the SQLite join algorithm or where i could find it (in what ".c" file). Thank for the help! ;) -- Cesar Rodas http://www.cesarodas.com/ Mobile Phone: 595 961 974165 Phone: 595 21 645590 [EMAIL PROTECTED] [EMAIL PROTECTED]
