Another note about this is the mention of large database sizes. MySQL and it's memory management, in my experience, are usually better at handling this type of thing. So, for a parent-child relationship of information which can contain say millions of lines of data, the database can probably handle it better than the application language can.
You are correct in your statement though, the database can be the most expensive part of your application. Additionally, your application processing comparisons can be the most expensive piece too. So I think that in order to answer this question its all about what data you are retrieving and what you want to do with it. In short, yes 2 program functions are more efficient than 2 sql queries iff the amount of resources required for the database to process outweigh that of the application. Thanks, Ryan Yagatich On Mon, 2003-06-16 at 16:32, Becoming Digital wrote: > It is generally faster to use your programming language for data processing. > However, if you can retrieve the data in a usable format with a single database > query, by all means, do it. Generally, developers seem to strive for as few > steps/operations as possible, whatever they may be. The difference in speed > comes when you're looking at two queries or two program functions; the latter is > almost always a great deal faster. > > Edward Dudlik > Becoming Digital > www.becomingdigital.com > > > ----- Original Message ----- > From: "Lingua2001" <[EMAIL PROTECTED]> > To: "Roman Neuhauser" <[EMAIL PROTECTED]> > Cc: "Ryan Yagatich" <[EMAIL PROTECTED]>; <[EMAIL PROTECTED]> > Sent: Monday, 16 June, 2003 16:23 > Subject: Re: several key values in one field? > > > 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] > > > -- ,_____________________________________________________, \ 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___\ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
