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]
