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
