On Fri, Jun 25, 2010 at 5:47 PM, Simon Slavin <slav...@bigfraud.org> wrote:
>
> I just want to check because this is a specialty of mine: do you perhaps
mean that you have indexed all the fields individually ? Because that's a
common mistake and it's a huge waste of time and space. There is one
particular INDEX which is ideal for each SELECT and SQL will use a maximum
of one INDEX for each simple SELECT command. If you show us your SELECT
command I can take a guess at an INDEX which will suit it best.
[Nilesh]
Nature of our data is hierarchical, so we are using adjacency model
(parent-id is a column).
e.g.
root { (aid 0)
a { (aid 0)
a1; (aid 0)
a2; (aid 1)
a3; (aid 2)
}
b { (aid 1)
b1; (aid 0)
b2; (aid 1)
}
}
id name parent aid value
0 root 0 0 x
1 a 0 0 x
2 b 0 1 x
3 a1 1 0 x
4 a2 1 1 x
5 a3 1 2 x
6 b1 2 0 x
7 b2 2 1 x
Practically the table could be very very huge. We have
usually query to walk all children of a parent so query
is
select * from table where parent = ? and aid = ?;
Our index is "create index idx on table (parent, aid)"
Thanks,
Nilesh
_______________________________________________
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
