Re: Seeking Opinions - Johan - com.mysql.lists.mysql

16 messages in com.mysql.lists.mysqlRe: Seeking Opinions

From	Sent On	Attachments
Robb Kerr	25 Nov 2005 08:59
SGr...@unimin.com	25 Nov 2005 10:01
Rhino	25 Nov 2005 10:16
Johan	25 Nov 2005 10:44
Hal Vaughan	25 Nov 2005 10:53
Robb Kerr	25 Nov 2005 12:43
Johan	25 Nov 2005 16:16
sheeri kritzer	28 Nov 2005 06:53
SGr...@unimin.com	28 Nov 2005 07:10
sheeri kritzer	28 Nov 2005 07:13
Martijn Tonies	28 Nov 2005 07:25
sheeri kritzer	28 Nov 2005 08:35
Martijn Tonies	28 Nov 2005 08:39
sheeri kritzer	28 Nov 2005 08:47
Martijn Tonies	28 Nov 2005 11:31
Harald Fuchs	29 Nov 2005 02:27

Subject:	Re: Seeking Opinions
From:	Johan (bees...@gmail.com)
Date:	11/25/2005 04:16:55 PM
List:	com.mysql.lists.mysql

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,

On 11/25/05, Robb Kerr <rker...@digitaliguana.com> 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,

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 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