MySQL Insert/Update across 3 tables with 1m+ rows

0 votes
1 answer
153 views
                          To start with, I know nothing of database design, so I apologise if this seems obvious to others. I have been researching up to 3NF over the last few weeks, and I think I have a layout that works.

I have a database with 1m+ rows, currently organised as follows:

Table: MasterTable  
Rows: ID, FirstName, LastName, PetName, PetAge

I would like to split it as follows:

Table: People  
PersonID (PK), FirstName, LastName

Table: Pets  
PetID (PK), PetName, PetAge

Table: Records  
RecordID (PK), MasterTable.ID, People.PersonID, Pets.PetID  
PKs in all cases auto-increment so that more records can be added later.

The people and pets tables have been populated using:

    INSERT INTO Pets(PetName, PetAge)
    SELECT PetName, PetAge
    From MasterTable
    WHERE 1

    INSERT INTO People(FirstName, LastName)
    SELECT PetName, PetAge
    From MasterTable
    WHERE 1

    INSERT INTO Records(ID)
    SELECT ID
    From MasterTable
    WHERE 1

So I have three tables. When I try to create the Records table, I can't get anything to work.
I have tried:

    INSERT INTO Records(PersonID, PetID, ID)
    SELECT People.PersonID, Pets.PetID, MasterTable.ID
    FROM MasterTable
    LEFT JOIN People ON MasterTable.FirstName = People.FirstName AND People.LastName = MasterTable.LastName
    LEFT JOIN Pets ON Pets.PetName = MasterTable.PetName AND Pets.PetAge = MasterTable.PetAge
    WHERE 1

I think the WHERE clause might be the problem. I have tried 
WHERE Pets.PetName = MasterTable.PetName
and almost every kind of WHERE I can think of.

I have a few questions I'd really appreciate some help with as I'm going out of my mind here.

1) Does it matter the order of the LEFT JOIN clauses? Does it matter which table is specified first and which is specified last?

2) I initially tried INNER JOIN but I figure it's just going to join more columns than is necessary, is that right?

3) If I am inserting firstname and lastname, I can't match on firstname and lastname, right? As in, create the firstname lastname entries and then use that ID to match the next join?

It seems simple enough to split this into three, assign a PK to each, and then create a finale table where PKs relate to PKs, but apparently it's not.

When I add '''LIMIT 5''' the select returns the correct info. Without the limit clause, all my attempts have run for over 24h and not finished. Either they have been stuck copying *everything* to temp tables, or they have just said "selecting data" as the status.

Can someone please help?

Sorry if something doesn't make sense, I'll clarify as I go.
                        
Asked by ernoh (1 rep)
Sep 3, 2020, 09:07 PM
Last activity: Jul 18, 2025, 10:04 AM
MySQL Insert/Update across 3 tables with 1m+ rows

Related Questions
