List:General Discussion« Previous MessageNext Message »
From:Sasha Pachev Date:July 8 1999 2:40am
Subject:Re: keyword search data structure
View as plain text  
Patrick Wirz wrote:
> 
> >From: Sasha Pachev <sasha@stripped>
> > > What would be the best (i.e. fastest, most efficient) way to assign
> about 5
> > > keywords to items in a catalog in order to search? Here's the scenario:
> > >
> > > I've got a database of images. I'd like to assign around 5 words
> describing
> > > what's in each image to enable searching. The database of images is used
> to
> > > create catalog pages (via PHP) so speed is very important.
> > >
> > > I seem to remember a discussion similar to this a few months ago but
> > > couldn't find it in the archives.
> > >
> > > Thanks,
> > > -Pat
> > >
> >
> > create table keywords ( id mediumint not null primary key, word
> > char(15));
> > create table img_keyword( img_id int not null, keyword_id mediumint not
> > null);
> >
> > --
> 
> Ok, I can see that this is probably the best solution space-wise and
> speed-wise, but let me ask a follow-up question. How poor would the
> performance be if you just added a CHAR field to the first table about 30-40
> characters long with the keywords in one string (and then do a sub-string
> search on that field)? I would guess that you wouldn't want to index that
> field so it would be slower, but how much slower? You can also assume that
> the number of images in the table would never exceed 1000. I'm just trying
> to think of ways to keep everything as simple as possible.
> 
> Thanks,
> Pat
> 

De-normalization for the sake of simplicity is  hardly ever worth it.
You may save yourself maybe a day of development, but you will most
likely regret it at some point. Even if the performance of a
de-normalized solution is acceptable, you are setting some very
artificial limits that could be easily avoided.

-- 
Sasha Pachev
http://www.sashanet.com
Thread
Re: Text Field Sizes and PHP (was: Access -> myODBC or -> textfile?)jawarren23 Jun
  • Re: Text Field Sizes and PHP (was: Access -> myODBC or ->textfile?)Mike Hanney23 Jun
    • keyword search data structurePatrick Wirz6 Jul
    • Re: keyword search data structureSasha Pachev7 Jul
    • Re: keyword search data structurePatrick Wirz7 Jul
    • Re: keyword search data structureSasha Pachev8 Jul