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]
