Dear Roman, I thought it was written in English....It wasn't? Here I'm sending the message again, Roman~~
***************My message 2****************************** Thank you, Ryan, And I'd like to know if, to use pure SQL is more efficient than to parse the multiple values in one field? What if the amount of the database is really huge? How about the performance speed comparing with the method using composite key tables as you suggested? Thanks in advance, again~~~ Lingua ----- Original Message ----- From: "Ryan Yagatich" <[EMAIL PROTECTED]> To: "Lingua2001" <[EMAIL PROTECTED]> Cc: <[EMAIL PROTECTED]> Sent: Monday, June 16, 2003 9:05 AM Subject: Re: several key values in one field? *************Ryan's reply 1***************************** > IMHO, I create link tables which have the many-to-many relationships as > you are looking for. Here's a small example > > CREATE TABLE users ( > iUserID INT(4) NOT NULL AUTO_INCREMENT, > cUsername VARCHAR(15) NOT NULL UNIQUE, > PRIMARY KEY(iUserID)); > > CREATE TABLE games ( > iGameID INT(4) NOT NULL AUTO_INCREMENT, > cGameName VARCHAR(25), > PRIMARY KEY (iGameID)); > > CREATE TABLE usersGamesLink ( > iLinkID INT(8) NOT NULL AUTO_INCREMENT, > iUserID INT(4) NOT NULL, > iGameID INT(4) NOT NULL, > PRIMARY KEY (iLinkID)); > > -- > > Now, for the SELECT statements, we can left join any games that belong > to an associated user. For example, say your username is 'ryan' > > SELECT games.cGameName > FROM games > LEFT JOIN usersGamesLink ON (usersGamesLink.iGameID=games.iGameID) > LEFT JOIN users ON (users.iUserID=usersGamesLink.iUserID) > WHERE users.cUsername='ryan'; > > There are more ways to adjust that query based on your intentions, so > that is not the most optimized query you can have. I'm just using it as > an example.... > > This will give you your many to many relationship without needing to > load all of the keys into memory and try to sort through them. to come > up with many other select statements for each one. > > Hopefully, this helps a bit, at least for giving you the right idea. > After you read this, I would recommend reading 'how mysql optimizes left > join and right join' > (http://www.mysql.com/doc/en/LEFT_JOIN_optimisation.html) and 'EXPLAIN > syntax (get information about a SELECT)' > (http://www.mysql.com/doc/en/EXPLAIN.html) > > Thanks, > Ryan Yagatich > ************My original message 1**************************** > On Sun, 2003-06-15 at 21:02, Lingua2001 wrote: > > Hi, > > > > while trying to handle severl key values, > > I wonder if it is more efficient to put > > those values in a row seperated by a > > certain seperator (eg. comma). > > > > The talbe has two fields and looks like; > > > > UID ForeinID > > 1 2,3,4,6 > > 2 7,9,4,5 > > 3 3,4 > > 4 1,5,7,9 > > 5 2,3,4,5 > > > > and the values of ForeignID is primary keys > > of another table. > > > > What would be the best way to handle this kind of > > case (i.e. M-to-M relationship) in MySQL? > > > > Thanks in advance. > > > > Lingua > -- > ,_____________________________________________________, > \ Ryan Yagatich [EMAIL PROTECTED] \ > / Pantek Incorporated (877) LINUX-FIX / > \ http://www.pantek.com/security (440) 519-1802 \ > / Are your networks secure? Are you certain? / > \___A9062F5C3EAE81D54A28A8C1289943D9EE43015BD8BC03F1___\ > > **************************Your (Roman) message 1********************** ----- Original Message ----- From: "Roman Neuhauser" <[EMAIL PROTECTED]> To: "Lingua2001" <[EMAIL PROTECTED]> Cc: "Ryan Yagatich" <[EMAIL PROTECTED]>; <[EMAIL PROTECTED]> Sent: Monday, June 16, 2003 3:15 PM Subject: Re: several key values in one field? > # [EMAIL PROTECTED] / 2003-06-16 14:47:19 -0500: > > Content-Type: text/plain; charset="ks_c_5601-1987" > > > > ?????????????????????????????????????????????????????????????????... > > posting in english would yield more replies. > > > -- > If you cc me or remove the list(s) completely I'll most likely ignore > your message. see http://www.eyrie.org./~eagle/faqs/questions.html > > -- > 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]
