Alexey Pechnikov <pechni...@mobigroup.ru> wrote:
> 2011/8/7 Simon Slavin <slav...@bigfraud.org>:
>> You don't need to. The SQLite expressions I listed tell you how to achieve
>> the result without doing that.
>
> Really? And how can you perform the query like to:
>
> sqlite> create table t1(ids text);
> sqlite> insert into t1 (ids) values ('1 2 3');
> sqlite> insert into t1 (ids) values ('2 3 4');
> sqlite> insert into t1 (ids) values ('3 4 5');
I can normalize this table, then use joins.
> A simple calculation: if each list of identifiers have about 1000
> items and there are
> 1 000 000 lists than the table of relations (t1.rowid, t2.rowid) will
> have 1 000 000 000
> rows!
One way or the other, you need to store 1,000,000,000 pieces of information.
Why is it that storing them in 1,000,000 rows holding 1000 items each is
unremarkable, but storing them in 1,000,000,000 rows holding one item each is
exclamation point-worthy?
If reducing the number of rows is your ultimate goal, why don't you create a
table with one row, holding the whole data structure encoded into one huge
string or blob? That'll best optimize the one metric you seem to believe
matters the most.
> It's too slow
... when compared to what alternative? Linearly scanning all those lists?
> Of cource all
> systems store lists of
> identifiers in similar situations.
I find it hard to believe that every single system does - surely systems exist
that do not denormalize their data this way. In fact, I doubt the design you
describe is common, let alone universally accepted.
--
Igor Tandetnik
_______________________________________________
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
