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