From: John Berman (john_bermanblueyonder.co.uk)
Date: Thu Nov 13 2003 - 15:52:15 CST

• Messages sorted by: [ date ] [ thread ] [ subject ] [ author ]

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:morsesinauer.com]
Sent: 13 November 2003 21:32
To: jbermanjewishgen.org
Cc: mysqllists.mysql.com
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

• Messages sorted by: [ date ] [ thread ] [ subject ] [ author ]