After hearing both sides being suggested, I think I would recommend the more normalized approach of having one or more separate tables for the names (one name per row) and a mapping table that associates a name field (mother's maiden name, decedent's given name, decedent's surname, etc.) on a record (marriage, birth, death, etc.) to the possible names the transcriptionist thinks were written on the record. A simple index would very quickly find all full matches and partial beginning matches. With mid-word or word-ending matches**, the engine will still need to scan the entire list of names (not every name in the database, just the unique list of spellings which will be much smaller) .
This (the multi-table design) may add a little complexity to your GUI code but I think that the performance improvement on the searches will more than offset a little extra effort added to the front end and data entry portions of your process. Shawn Green Database Administrator Unimin Corporation - Spruce Pine ** You could optimize word-ending searches by storing a copy of the name reversed. Then a regular index could also be used to quickly find the end of a name. Lets say you want to find all of the names that end in "esh"... You have names like "Abesh", "Janesh", and "Phresh". If you added another column (indexed of course) to your names table that stored the names as "hsebA", "hsenaJ", and "hserhP" a search using LIKE 'hse%' would use that index where a search using LIKE '%esh' could not. The trade off is that you nearly double the storage size of your names tables (one extra column plus its index) but you seriously improve search performance for an entire class of queries (names ending with ....). "Jigal van Hemert" <[EMAIL PROTECTED]> wrote on 01/06/2005 09:30:08 AM: > From: "Kentucky Families" > > > A full-text search won't work. Too many records -- at least 8 million. > From what I've read, full-text won't pick up the individual initials or very > short names, like Vu, Lee, Doe, etc. > > You can set the ft_min_word_len in an option file in the [mysqld] section to > change the minimum word length (rebuild the full text indexes afterwards!) > > Maybe alter the data a bit? > If you store only firstnames you can search for J* to pick up an initial. > If you only know initials simply use Jxzx as a first name (and let your > application filter out the xzx part) > If a name is shorter than four letters add xzx to the name and filter out in > the application that shows the data. > > Regards, Jigal. > > > -- > MySQL General Mailing List > For list archives: http://lists.mysql.com/mysql > To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] >
