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]
