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
