I did answer my own question, and that's why I said:
> 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?
Using a SET is, I believe, MUCH faster than making a special table
simply to normalize the data. I believe in using MySQL's
functionality to the fullest, and working smarter, not harder.
Would folks use SET to help normalize their data instead of using a
separate table? Should I put in a request for the feature I want? Or
would very few folks benefit, and it would add too much storage and
computational cost? (my original question)
-Sheeri
On 11/28/05, [EMAIL PROTECTED] <[EMAIL PROTECTED]> wrote:
>
> 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]
> >
>
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
