[prev in list] [next in list] [prev in thread] [next in thread] 

List:       sqlite-users
Subject:    [sqlite] extremely slow join on an fts3 table
From:       "Jos van den Oever" <jvdoever () gmail ! com>
Date:       2008-12-03 14:57:32
Message-ID: c2dbc4260812030657m1cbade8ey6b658c68c59cbc71 () mail ! gmail ! com
[Download RAW message or body]

Hi all,

Doing a join on a fts3 table can be very slow. I'm using these tables:

CREATE TABLE general (
  ID INTEGER PRIMARY KEY AUTOINCREMENT NOT NULL,
  ...
);
CREATE VIRTUAL TABLE general_text using fts3 (
  ID INTEGER PRIMARY KEY AUTOINCREMENT NOT NULL,
  a TEXT,
  b TEXT,
  c TEXT,
  d TEXT,
  e TEXT DEFAULT '',
  f TEXT
);

which creates these tables

CREATE TABLE general_text_content(  docid INTEGER PRIMARY KEY,c0ID,
c1a, c2b, c3c, c4d, c5e, c6f);
CREATE TABLE general_text_segdir(  level integer,  idx integer,
start_block integer,  leaves_end_block integer,  end_block integer,
root blob,  primary key(level, idx));
CREATE TABLE general_text_segments(  blockid INTEGER PRIMARY KEY,  block blob);

and fill them with about half a million rows.

On these tables this query is very slow (about 1 row per second)

select g.id  from general g, general_text gt where g.id = gt.id;

and these ones have a normal speed:

select g.id  from general g, general_text_content gt where g.id = gt.docid;
select g.id  from general g, general_text_content gt where g.id = gt.c0ID;

What is causing the fts3 code to make joining so slow?

Cheers,
Jos
_______________________________________________
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[prev in list] [next in list] [prev in thread] [next in thread]