[KPhotoAlbum] Idea for quick search feature

Tuomas Suutari thsuut at utu.fi
Mon Apr 23 16:59:27 CEST 2007


On Monday 23 April 2007 16:24, Shawn Willden wrote:
> Does the basic idea make sense?

Yeah, great idea. :)


> Do you have any suggestions for implementation, especially related to
> how to make it fast?

For smaller databases speed shouldn't be a problem, but making it fast 
could be quite hard for larger databases:

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
   );

Results:
search string	| database	| result set size	| time / s
------------------------------------------------------------------
%a%		| My own	| 4929			| 0.09
%tuo%		| My own	| 4011			| 0.30
%tuomas%	| My own	| 3946			| 0.31
%ris%		| My own	| 74			| 0.40
%a%		| Risto's	| 43256			| 7.78
%tuo%		| Risto's	| 11			| 20.81
%tuomas%	| Risto's	| 0			| 19.99
%ris%		| Risto's	| 2297			| 20.43

So DB's size seems to affect query times A LOT. :-/

With XML backend things should be fast, because everything is in RAM.

-- 
Tuomas Suutari | +358 50 3806983 | thsuut at utu.fi


More information about the KPhotoAlbum mailing list