[Digikam-devel] [Bug 160966] some searches don't work properly with the new sqlite3-3.5.8

[Andi Clemens]

------- You are receiving this mail because: -------
You are the assignee for the bug, or are watching the assignee.
         
http://bugs.kde.org/show_bug.cgi?id=160966         




------- Additional Comments From andi.clemens gmx net  2008-04-26 11:57 -------
Two more queries, maybe this is helpful.
The first query is very fast, it looks in a specific folder for images without a given tag:

SELECT Albums.url||'/'||Images.name FROM Images, Albums LEFT JOIN ImageProperties on Images.id = ImageProperties.imageid WHERE (  (Images.dirid = '39')  AND  (Images.id NOT IN    (SELECT ImageTags.imageid FROM ImageTags JOIN TagsTree on ImageTags.tagid = TagsTree.id     WHERE TagsTree.pid = '25' or ImageTags.tagid = '25' ))  )  AND (Albums.id=Images.dirid)  LIMIT 500;

The next one is incredibly slow, it took nearly 5 minutes!!!! It is a simple search looking for the family name "Saala":

SELECT Images.id, Images.name, Images.dirid, Images.datetime, Albums.url FROM Images, Albums LEFT JOIN ImageProperties ON Images.id = Imageproperties.imageid WHERE ( ( (Images.dirid IN   (SELECT id FROM Albums WHERE url LIKE '%Saala%'))  OR  (Images.name LIKE '%Saala%')  OR  (Images.id IN    (SELECT ImageTags.imageid FROM ImageTags JOIN TagsTree on ImageTags.tagid = TagsTree.id     WHERE TagsTree.pid = (SELECT id FROM Tags WHERE name LIKE 'Saala')     OR ImageTags.tagid = (SELECT id FROM Tags WHERE name LIKE 'Saala') ))  OR  (Images.dirid IN   (SELECT id FROM Albums WHERE caption LIKE '%Saala%'))  OR  (Images.dirid IN   (SELECT id FROM Albums WHERE collection LIKE '%Saala%'))  OR  (Images.caption LIKE '%Saala%')  OR  (ImageProperties.value LIKE '%Saala%' and ImageProperties.property='Rating') ) )  AND (Albums.id=Images.dirid);


So searching for ratings is not the only issue with the new sqlite version. Arnd, I will mail my DB to you (2.5 MB)