multiple values in one column
Hi Using MySql 3.23 Can I have multiple values in one column and then index the column, I have used different delimiters but the index only seems find the whole contents of the column no matter what I separate the data with Regards John Berman [EMAIL PROTECTED]
Re: multiple values in one column
On Thursday, Nov 13, 2003, at 02:55 US/Eastern, John Berman wrote: Can I have multiple values in one column and then index the column, I have used different delimiters but the index only seems find the whole contents of the column no matter what I separate the data with It seems like you'd be better off with a modified database design. If you need multiple values in a column, you might be better off making an intersection table, and indexing that. So instead of having multiple values in column 1 of table A, you have multiple rows in table B, each with one value, referencing a single row in table A. (I hope I've explained that clearly; if I've misunderstood, or someone has a better way of phrasing it, feel free to jump in.) pjm PGP.sig Description: This is a digitally signed message part
RE: multiple values in one column
Hm Lost me a bit, if you can all bear with me I will give a little more info I have a single table with 120 fields (its full of genealogical data) All the records apart from marriages have an entry in the surname field Every record has a set identifier (uniqueref) When I search I have a statement like: sql = SELECT COUNT(*) AS res, uniqueref FROM global WHERE surname = ' globsurname ' group by uniqueref As you can see the restuls are grouped by the identifier. The surname coloum is indexed and we have 200,000 records and get a result within a couple of seconds. The results are tabulated on a web page and then one can drill down Go to http://www.jgsgb.org.uk/members/databasex.asp (username and password is: berman) Now marriages don't have an entry in surname but they do in groomsurname and bridesurname, I figured if I copied the groom surname and bridesurname to the main surname index that would do the trick ? I did index groomsurname and bridesurname and then use a statement like sql = SELECT COUNT(*) AS res, uniqueref FROM global WHERE surname = ' globsurname ' or groomsurname = ' globsurname ' or bridesurname = ' globsurname 'group by uniqueref This gave me 2 problems, It really slowed down the search and if a result was found I could not detrmine which field it was found in so drilling down was a problem. Any help would be appreciated. Regards John Berman -Original Message- From: Parker Morse [mailto:[EMAIL PROTECTED] Sent: 13 November 2003 14:14 To: [EMAIL PROTECTED] Cc: [EMAIL PROTECTED] Subject: Re: multiple values in one column On Thursday, Nov 13, 2003, at 02:55 US/Eastern, John Berman wrote: Can I have multiple values in one column and then index the column, I have used different delimiters but the index only seems find the whole contents of the column no matter what I separate the data with It seems like you'd be better off with a modified database design. If you need multiple values in a column, you might be better off making an intersection table, and indexing that. So instead of having multiple values in column 1 of table A, you have multiple rows in table B, each with one value, referencing a single row in table A. (I hope I've explained that clearly; if I've misunderstood, or someone has a better way of phrasing it, feel free to jump in.) pjm -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: multiple values in one column
On Thursday, Nov 13, 2003, at 12:03 US/Eastern, John Berman wrote: I have a single table with 120 fields (its full of genealogical data) All the records apart from marriages have an entry in the surname field [snip] Now marriages don't have an entry in surname but they do in groomsurname and bridesurname, I figured if I copied the groom surname and bridesurname to the main surname index that would do the trick ? I did index groomsurname and bridesurname and then use a statement like sql = SELECT COUNT(*) AS res, uniqueref FROM global WHERE surname = ' globsurname ' or groomsurname = ' globsurname ' or bridesurname = ' globsurname 'group by uniqueref This gave me 2 problems, It really slowed down the search and if a result was found I could not detrmine which field it was found in so drilling down was a problem. Wow. I think I had suspected this might be the problem. I don't think there's any way of solving the two problems of really slowing down the search and not knowing which column matched without fundamentally changing your table structure. It sounds like you've got the whole database in one table, which isn't really making use of the strengths of the DBMS. You'd do much better breaking this data out into multiple related tables. You'd still have to do multiple queries while looking for surnames, but the collected queries on the (much smaller) tables would be still be faster than the single query I've quoted above. Without knowing what columns you have in your current table, I can't suggest how you'd break them down, but a good rule of thumb is that each table should contain only one kind of entity. So you probably shouldn't have marriages and individual persons in one table. (Far better to have a table of people, then a table of marriages which describes the relationships? Then you don't have bridesurname and groomsurname fields, just one surname field. You can also describe multiple marriages for a single person with a minimum of table space.) This isn't the place (nor am I the right person, probably) to get into an extended discussion of database normalization, but if you search that phrase on the web you'll find quite a bit. Here's one place to start: http://databases.about.com/ (look under design). Many MySQL books will discuss normalization as well (it's in chapter 7 of the O'Reilly book I have here.) pjm PGP.sig Description: This is a digitally signed message part
RE: multiple values in one column
Thanks for the reply, I'm getting the message A shorter question I have seen that you can create an index across multiple columns, is there any way one can tell which column returned the results (this would help me short term). Or indeed have more than one word that's indexed in a single column. Regards John Berman -Original Message- From: Parker Morse [mailto:[EMAIL PROTECTED] Sent: 13 November 2003 21:32 To: [EMAIL PROTECTED] Cc: [EMAIL PROTECTED] Subject: Re: multiple values in one column On Thursday, Nov 13, 2003, at 12:03 US/Eastern, John Berman wrote: I have a single table with 120 fields (its full of genealogical data) All the records apart from marriages have an entry in the surname field [snip] Now marriages don't have an entry in surname but they do in groomsurname and bridesurname, I figured if I copied the groom surname and bridesurname to the main surname index that would do the trick ? I did index groomsurname and bridesurname and then use a statement like sql = SELECT COUNT(*) AS res, uniqueref FROM global WHERE surname = ' globsurname ' or groomsurname = ' globsurname ' or bridesurname = ' globsurname 'group by uniqueref This gave me 2 problems, It really slowed down the search and if a result was found I could not detrmine which field it was found in so drilling down was a problem. Wow. I think I had suspected this might be the problem. I don't think there's any way of solving the two problems of really slowing down the search and not knowing which column matched without fundamentally changing your table structure. It sounds like you've got the whole database in one table, which isn't really making use of the strengths of the DBMS. You'd do much better breaking this data out into multiple related tables. You'd still have to do multiple queries while looking for surnames, but the collected queries on the (much smaller) tables would be still be faster than the single query I've quoted above. Without knowing what columns you have in your current table, I can't suggest how you'd break them down, but a good rule of thumb is that each table should contain only one kind of entity. So you probably shouldn't have marriages and individual persons in one table. (Far better to have a table of people, then a table of marriages which describes the relationships? Then you don't have bridesurname and groomsurname fields, just one surname field. You can also describe multiple marriages for a single person with a minimum of table space.) This isn't the place (nor am I the right person, probably) to get into an extended discussion of database normalization, but if you search that phrase on the web you'll find quite a bit. Here's one place to start: http://databases.about.com/ (look under design). Many MySQL books will discuss normalization as well (it's in chapter 7 of the O'Reilly book I have here.) pjm -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
