You, yourself, explain why your suggestion would be a bad fit for this
project: SET is limited to just 64 discrete values per table.
I cannot remember reading that increasing the size of the SET features as
a priority on any development list. I might have missed something but I
don't think that SET>64 will be coming any time soon.
Shawn Green
Database Administrator
Unimin Corporation - Spruce Pine
sheeri kritzer <[EMAIL PROTECTED]> wrote on 11/28/2005 09:53:49 AM:
> 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 <[EMAIL PROTECTED]> 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 <[EMAIL PROTECTED]> 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/[EMAIL PROTECTED]
> > >
> > >
> >
> >
>
> --
> MySQL General Mailing List
> For list archives: http://lists.mysql.com/mysql
> To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
>
