[KPhotoAlbum] Idea for quick search feature

Shawn Willden shawn-kimdaba at willden.org
Wed Apr 25 16:10:31 BST 2007

On Monday 23 April 2007 08:59:27 am Tuomas Suutari wrote:
> I tested few SQL querys to implement this kind of behaviour with my own
> DB (5544 files) and Risto's DB (43277 files).
> Querys were like this one (only %a% replaced):
> SELECT id FROM media
> WHERE filename LIKE '%a%'
>    OR label LIKE '%a%'
>    OR description LIKE '%a%'
>    OR dirid IN (
>       SELECT id
>       FROM dir
>       WHERE path LIKE '%a%'
>    )
>    OR id IN (
>       SELECT mediaid
>       FROM media_tag, tag
>       WHERE tag.name LIKE '%a%'
>         AND media_tag.tagid=tag.id
>    );

One comment about this query:  It's consistent with my initial specification, 
but I'm questioning the value of searching for strings within tags.  The 
current search bar always matches prefixes.  Description and label searches 
should probably be full-text, but I think a search for "m" should match a 
tag "mary" but not "sam".

Does that make sense?  It does to me, and it makes some performance 
improvements for in-memory databases possible.  Similarly, I would think a 
SQL DB with indexes on tag names would be much faster at searching for prefix 
matches, too.


More information about the Kphotoalbum mailing list