Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

We have a large table of contacts (8 million records) that we promote to with mailings. When these people register we store them in a different members table. But we want to go back and update the contacts table each day with the members table ID for each conversion. I added a member_id column to the contacts table but I can't really use a member_id foreign key on that column as most will have a null value.

I'm trying to determine what the best type of index might be used on the member_id column to speed up queries on those people. Currently i have the column nullable with a unique index but queries on those people are taking several minutes which isn't very performant. Wondering if some more experienced people out there have dealt with this type of issue?

share|improve this question
add comment

1 Answer 1

up vote 1 down vote accepted

Perhaps a cross-reference table would be the most prudent way to go. One that has the ID from contacts, and the ID from members. This way you know who has converted, and can keep the table as small as necessary.

As an example (I use SQL Server, not MySQL):

CREATE TABLE Members
(
    MemberID INT
    , MemberData varchar(255)
);

CREATE TABLE Contacts
(
    ContactID INT
    , ContactDate datetime
    , ContactData varchar(255)
);

CREATE TABLE MembersContactsXRef
(
    MemberID INT
    , ContactID INT
);

I would insert rows into MembersContactsXRef each time a conversion occurred for a given member. Both fields in MembersContactsXRef could be foreign keys.

share|improve this answer
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.