List:General Discussion« Previous MessageNext Message »
From:sheeri kritzer Date:November 28 2005 3:13pm
Subject:Re: Seeking Opinions
View as plain text  
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, SGreen@stripped <SGreen@stripped> 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 <awfief@stripped> 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 <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
>  > > >
>  > > >
>  > >
>  > >
>  >
>  > --
>  > 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