Did anyone do some benchmarks how the insert-speed of FTS compares to a TEXT
INDEX column? I don't need many of the extra features of FTS, because I
always need to look up rows by prefix or exact match, and both can be
implemented efficiently via TEXT INDEX too. But if the overhead is
comparable,
, 2011 9:20 AM
To: General Discussion of SQLite Database
Subject: EXT :[sqlite] FTS vs INDEX
Did anyone do some benchmarks how the insert-speed of FTS compares to a TEXT
INDEX column? I don't need many of the extra features of FTS, because I
always need to look up rows by prefix or exact match
]
Sent: Wednesday, October 19, 2011 9:20 AM
To: General Discussion of SQLite Database
Subject: EXT :[sqlite] FTS vs INDEX
Did anyone do some benchmarks how the insert-speed of FTS compares to a
TEXT
INDEX column? I don't need many of the extra features of FTS, because I
always need to look up rows
FTS use index multi-tree and de-facto has _no_ insert speed degradation.
I did do test for 400+ millions of records.
With b-tree index there is insert speed degradation:
http://geomapx.blogspot.com/2010/04/sqlite-index-degradation-tests.html
http://geomapx.blogspot.com/search?q=index+speed
So FTS
2011/10/19 Alexey Pechnikov pechni...@mobigroup.ru
FTS use index multi-tree and de-facto has _no_ insert speed degradation.
Thanks, that's good to hear! It makes me wonder why SQLite doesn't use that
same multi-tree mechanism for regular indexes, but that's a whole different
question.
2011/10/19 Fabian fabianpi...@gmail.com:
Thanks, that's good to hear! It makes me wonder why SQLite doesn't use that
same multi-tree mechanism for regular indexes, but that's a whole different
question.
It's impossible with SQLite3 database format. May be SQLite4 will be
support it :)
--
: [sqlite] FTS vs INDEX
Very interesting benchmarks! However it seems to focus mainly on the speed
of SELECT queries, and the total size of the resulting database on disk. But
my main concern is about the speed of INSERT queries vs normal tables. Any
chance you compared that too
On Wed, Oct 19, 2011 at 7:56 AM, Fabian fabianpi...@gmail.com wrote:
2011/10/19 Alexey Pechnikov pechni...@mobigroup.ru
FTS use index multi-tree and de-facto has _no_ insert speed degradation.
Thanks, that's good to hear! It makes me wonder why SQLite doesn't use that
same multi-tree
2011/10/19 Scott Hess sh...@google.com
To be clear, how it works is that new insertions are batched into a
new index tree, with index trees periodically aggregated to keep
selection efficient and to keep the size contained. So while the
speed per insert should remain pretty stable constant,
On Wed, Oct 19, 2011 at 12:50 PM, Fabian fabianpi...@gmail.com wrote:
2011/10/19 Scott Hess sh...@google.com
To be clear, how it works is that new insertions are batched into a
new index tree, with index trees periodically aggregated to keep
selection efficient and to keep the size contained.
2011/10/19 Fabian fabianpi...@gmail.com:
I always do inserts in batches of 100.000 rows, and after each batch I
manually merge the b-trees using:
INSERT INTO table(table) VALUES('optimize');
Is there a possibility that it will do automatic maintenance half-way during
a batch? Or will it
11 matches
Mail list logo
