List:General Discussion« Previous MessageNext Message »
From:sheeri kritzer Date:November 28 2005 2:53pm
Subject:Re: Seeking Opinions
View as plain text  
On this note, I feel as though the best way to do this in MySQL is to
use the ENUM or SET types, instead of indexing against a separate
table  -- I'm guessing SET, so each piece of clipart can be associated
with more than one category.  Of course, that eliminates relevancy
searches (which you could do as in the first example given by the OP,
with a text field and fulltext search).

I think the biggest problem with that is that SET only allows up to 64
different items, so the clipart site could only have up to 64
categories (and a Christmas floral arrangement might have the keywords
"color", "flowers" and "Christmas").

That being said, the point of this post is to ask -- Is MySQL working
on allowing the SET limit to increase?  Or is that just too much
storage and math?

-Sheeri

On 11/25/05, Johan <beest1967@stripped> wrote:
> Hi Robb,
>
> Your table setup is correct. In the third table the two ID fields together
> form the Primary Key. This makes sure that you cannot add the same keyword
> twice to the same image. If you use MySQL Administrator to create your
> tables then you just add both columns to the primary key index.
> Programmatically it would look something like (to create the tabloe and
> indexes):
>
> CREATE TABLE "ClipartKeyword" (
>    "ClipartID" INT(11) NOT NULL,
>    "KeywordID" INT(11) NOT NULL,
>    PRIMARY KEY  ("ClipartID","KeywordID"),
>    INDEX ("KeywordID")
>    ) TYPE=InnoDB;
>
> This database uses ANSI, hence the quotes around the field names. The index
> on KeywordID makes it easy to find all images that have a certain keyword
> attached to it.
>
> Good luck,
>
> Johan
>
> On 11/25/05, Robb Kerr <rkerr.news@stripped> wrote:
> >
> > On Fri, 25 Nov 2005 10:44:44 -0800, Johan wrote:
> >
> > >> Option One
> > >> Related tables. Table one (clipart pieces) contains ClipartID and
> > >> ClipartName fields. Table two (keywords) contains KeywordID, ClipartID
> > and
> > >> Keyword fields. This option will create an incredibly large related
> > table
> > >> (keywords) with each piece of clipart having tens of related fields in
> > the
> > >> keyword table. But, searching ought to be fast.
> > >
> > >
> > > Use this option but use a third table that contains just ClipartID and
> > > KeywordID to create the m:n relationship. Like this:
> > >
> > > Clipart: ClipartID (primary key) & Clipartname
> > > Keywords: KeywordID (primary key) & Keyword (just one so must be
> unique)
> > > Linktable: ClipartID & KeywordID (ClipartID + KeywordID = primary key)
> > >
> > > I have a database like this with over 250,000 images, 50,000+ keywords
> > and
> > > more than 2 million image - keyword links. All my keyword searches are
> > very
> > > fast (under 0.05 seconds per query).
> > >
> > > This is on very mediocre hardware (AMD Sempron 2600+ with only 512Mb of
> > > memory) so performance on a faster computer with more memory should be
> > > excellent.
> > >
> > > HTH,
> > >
> > > Johan
> >
> > Please explain further your 3 table scenario. Is the following example
> > correct...
> >
> > Table One - Clipart
> > ClipartID (primary key)  |  ClipartName
> > 1                        |  artone.jpg
> > 2                        |  arttwo.jpg
> > 3                        |  artthree.jpg
> >
> > Table Two - Keywords
> > KeywordID (primary key)  |  Keyword
> > 1                        |  black and white
> > 2                        |  color
> > 3                        |  christmas
> > 4                        |  thanksgiving
> >
> > Table Three - LinkTable
> > ClipartID                |  KeywordID
> > 1                        |  1
> > 1                        |  3
> > 2                        |  2
> > 2                        |  3
> >
> > I don't understand what would be the primary key for the third table or
> > what you mean by "(ClipartID + KeywordID = primary key)". Please
> > elaborate.
> >
> > Thanx,
> > Robb
> >
> > --
> > MySQL General Mailing List
> > For list archives: http://lists.mysql.com/mysql
> > To unsubscribe:    http://lists.mysql.com/mysql?unsub=1
> >
> >
>
>
Thread
Seeking OpinionsRobb Kerr25 Nov
  • Re: Seeking OpinionsSGreen25 Nov
  • Re: Seeking OpinionsRhino25 Nov
  • Re: Seeking OpinionsJohan25 Nov
    • Re: Seeking OpinionsHal Vaughan25 Nov
  • Re: Seeking OpinionsRobb Kerr25 Nov
    • Re: Seeking OpinionsJohan26 Nov
      • Re: Seeking Opinionssheeri kritzer28 Nov
        • Re: Seeking OpinionsSGreen28 Nov
          • Re: Seeking Opinionssheeri kritzer28 Nov
        • Re: Seeking OpinionsMartijn Tonies28 Nov
          • Re: Seeking Opinionssheeri kritzer28 Nov
        • Re: Seeking OpinionsMartijn Tonies28 Nov
          • Re: Seeking Opinionssheeri kritzer28 Nov
        • Re: Seeking OpinionsMartijn Tonies28 Nov
        • Re: Seeking OpinionsHarald Fuchs29 Nov