[sqlite] Re: how can i optimize this query

[Cesar David Rodas Maldonado]

The query SQL query that I sent represents the next search : word1 word2
"word3 word4"



On 8/22/06, Cesar David Rodas Maldonado <[EMAIL PROTECTED]> wrote:

I have the next table with about 10.000.000 of records-

CREATE TABLE ft_index (
  docid int(11) NOT NULL default '0',
  wordid int(11) NOT NULL default '0',
  posicion int(11) NOT NULL default '0',
  ranking float NOT NULL default '0',
  lang int(11) NOT NULL default '0',
  KEY docid (docid,wordid,posicion,ranking),
  KEY lang (lang)
);

How can i optimize the next query, couse i need velocity (this is for a
fulltext search project):


select
t0.*,
t0.ranking + t1.ranking + t2.ranking + t3.ranking + t4.ranking as ranking
from ft_index as t0
inner join ft_index as t1 on (t0.docid = t1.docid)
inner join ft_index as t2 on (t0.docid = t2.docid)
inner join ft_index as t3 on (t0.docid = t3.docid)
inner join ft_index as t4 on (t0.docid = t4.docid)
where (t0.wordid = '18929') AND (t1.wordid = '27283') AND( t2.wordid =
'4351' and t2.posicion + 1 = t3.posicion and t3.wordid = '9418' and
t3.posicion + 1 = t4.posicion ) group by t0.docid order by ranking;

Every inner join is for search a word that i save in another table (with
the number of words).