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]