Hi,
Sorry for the delay. There are various problems: 1) H2 doesn't
currently optimize queries with OR very well. 2) You should probably
add more indexes 3) You should run ANALYZE to help the query
optimizer.
So I suggest to do this:
drop all objects;
CREATE TABLE item (id INT PRIMARY KEY AUTO_INCREMENT, path VARCHAR);
CREATE TABLE node (fk_item INT, fk_parent INT, lft INT, rgt INT, leaf
BOOLEAN, FOREIGN KEY(fk_item) REFERENCES item(id), FOREIGN
KEY(fk_parent) REFERENCES node(fk_item));
INSERT INTO item (id, path) values (1, 'root');
INSERT INTO item (id, path) values (2, 'file');
INSERT INTO item (id, path) values (23, 'vz1');
INSERT INTO item (id, path) values (45, 'vz2');
INSERT INTO item (id, path) values (29, 'vz1_1');
INSERT INTO item (id, path) values (39, 'vz1_2');
INSERT INTO item (id, path) values (31, 'bild1.png');
INSERT INTO item (id, path) values (35, 'bild2.png');
INSERT INTO item (id, path) values (41, 'bild.png');
INSERT INTO item (id, path) values (25, 'bild.png');
INSERT INTO item (id, path) values (47, 'bild.png');
INSERT INTO node (fk_item, fk_parent, lft, rgt, leaf) values (1, null,
1, 36, false);
INSERT INTO node (fk_item, fk_parent, lft, rgt, leaf) values (2, 1,
16, 35, false);
INSERT INTO node (fk_item, fk_parent, lft, rgt, leaf) values (23, 2,
21, 34, false);
INSERT INTO node (fk_item, fk_parent, lft, rgt, leaf) values (45, 2,
17, 20, false);
INSERT INTO node (fk_item, fk_parent, lft, rgt, leaf) values (29, 23,
26, 31, false);
INSERT INTO node (fk_item, fk_parent, lft, rgt, leaf) values (39, 23,
22, 25, false);
INSERT INTO node (fk_item, fk_parent, lft, rgt, leaf) values (31, 29,
29, 30, true);
INSERT INTO node (fk_item, fk_parent, lft, rgt, leaf) values (35, 29,
27, 28, true);
INSERT INTO node (fk_item, fk_parent, lft, rgt, leaf) values (41, 39,
23, 24, true);
INSERT INTO node (fk_item, fk_parent, lft, rgt, leaf) values (25, 23,
32, 33, true);
INSERT INTO node (fk_item, fk_parent, lft, rgt, leaf) values (47, 45,
18, 19, true);
create index idx_node_rgt on node(rgt);
create index idx_node_lft on node(lft);
create index idx_item_path on item(path);
analyze;
SELECT cn2.fk_item, ci2.path
FROM node cn0, item ci0, node cn1, item ci1, node cn2, item ci2
WHERE ci0.id=cn0.fk_item AND cn0.lft>16 AND cn0.rgt<35
AND cn0.fk_item=cn1.fk_parent AND cn1.fk_item=ci1.id AND ci1.path like 'vz1_1'
AND cn1.fk_item=cn2.fk_parent AND cn2.fk_item=ci2.id AND ci2.path like
'bild%.png'
union
select cn2.fk_item, ci2.path
FROM node cn0, item ci0, node cn1, item ci1, node cn2, item ci2
where ci0.id=cn0.fk_item AND cn0.lft>16 AND cn0.rgt<35
AND cn0.fk_item=cn1.fk_parent AND cn1.fk_item=ci1.id AND ci1.path like 'vz1_2'
AND cn1.fk_item=cn2.fk_parent AND cn2.fk_item=ci2.id AND ci2.path like
'bild%.png';
-- 27 ms
SELECT cn2.fk_item, ci2.path
FROM node cn0, item ci0, node cn1, item ci1, node cn2, item ci2
WHERE ci0.id=cn0.fk_item AND cn0.lft>16 AND cn0.rgt<35
AND cn0.fk_item=cn1.fk_parent AND cn1.fk_item=ci1.id AND ci1.path in
('vz1_1', 'vz1_2')
AND cn1.fk_item=cn2.fk_parent AND cn2.fk_item=ci2.id AND ci2.path like
'bild%.png';
-- 11 ms
SELECT cn2.fk_item, ci2.path
FROM node cn0, item ci0, node cn1, item ci1, node cn2, item ci2
WHERE ((
ci0.id=cn0.fk_item AND cn0.lft>16 AND cn0.rgt<35
AND cn0.fk_item=cn1.fk_parent AND cn1.fk_item=ci1.id AND ci1.path like 'vz1_1'
AND cn1.fk_item=cn2.fk_parent AND cn2.fk_item=ci2.id AND ci2.path like
'bild%.png'
) OR (
ci0.id=cn0.fk_item AND cn0.lft>16 AND cn0.rgt<35
AND cn0.fk_item=cn1.fk_parent AND cn1.fk_item=ci1.id AND ci1.path like 'vz1_2'
AND cn1.fk_item=cn2.fk_parent AND cn2.fk_item=ci2.id AND ci2.path like
'bild%.png'
));
-- 774 ms
--
You received this message because you are subscribed to the Google Groups "H2
Database" group.
To post to this group, send email to [email protected].
To unsubscribe from this group, send email to
[email protected].
For more options, visit this group at
http://groups.google.com/group/h2-database?hl=en.
