As the question reads, I am building a database with 3 tables. Now these tables are going to be used to store names.

  • Table 1 will store First Names
  • Table 2 will store Last Names
  • Table 3 will be a one to one table linking the First names to the Last names

Now all this data will be coming from a text file in the format of:

Firstname MI Lastname

I estimate that their will be over 100 million records. I also don't want to do a insert for every name it will be an update record on duplicate Key. So what is the most optimal way to enter this into my database. By the way it is a Innodb so the whole table gets locked so I can't do multiple insert updates at a time

Now this whole process will be done via C# using mysql connection and I have:

sqlRequest += "START TRANSACTION ;" +
    "UPDATE firstName SET LastUpdated = CURRENT_TIMESTAMP WHERE first= '" + countSplit[0] +"' ;" +
    "INSERT INTO firstName(first, LastUpdated) SELECT '" + countSplit[0] + "' AS first, CURRENT_TIMESTAMP AS LastUpdated FROM dual WHERE NOT EXISTS ( SELECT * FROM firstName d WHERE d.first= '" + countSplit[0] + "') ;" +
    "COMMIT ;";
sqlRequest += "START TRANSACTION ;" +
    "UPDATE lastName SET LastUpdated = CURRENT_TIMESTAMP WHERE last = '" + countSplit[2] + "' ;" +
    "INSERT INTO lastName (last, LastUpdated) SELECT '" + countSplit[2] + "' AS last, CURRENT_TIMESTAMP AS LastUpdated FROM dual WHERE NOT EXISTS ( SELECT * FROM lastName d WHERE d.last = '" + countSplit[2] + "') ;" +
    "COMMIT ;";
sqlRequest += "START TRANSACTION ;" + 
    "INSERT INTO first_to_last " +
    "(firstid,lastid,LastUpdated) VALUES "+
    "((SELECT firstid FROM firstName WHERE first='" + countSplit[0] + "')," +
    "(SELECT lastid FROM lastName WHERE last='" + countSplit[2] + "' )," +
    "COMMIT ;";

So do you think that this is the best way? Or do you think there is something better?

  • 1
    \$\begingroup\$ Any reason why you're implementing the database like this where all you need is one table with a Primary Key, First Name, Last Name columns? \$\endgroup\$
    – Mataniko
    Commented Dec 3, 2012 at 4:24
  • \$\begingroup\$ Building a application that requires this layout \$\endgroup\$
    – Dustin Taggart
    Commented Dec 3, 2012 at 5:21
  • \$\begingroup\$ Can you be more specific on your application? The database design shouldn't be affected by the application design as you can display the data in any way you like. This sounds like a relational database homework assignment to show the use of foreign keys. \$\endgroup\$
    – Mataniko
    Commented Dec 3, 2012 at 5:29
  • \$\begingroup\$ Well to be honest I am working on this to be plugged into a already existing system. This is only a small part, my employer suggested I ask here but not to reveal anything relating to the project. I have it working but it takes a long time to build. I am looking for suggestions on speed performance. \$\endgroup\$
    – Dustin Taggart
    Commented Dec 3, 2012 at 6:11
  • \$\begingroup\$ Performance could be related to how the tables are defined, and you can expect a hit on every query since the system will require to select on three tables, possibly use Joins for sane results, etc. With this implementation. As for the queries themselves, you are basically scanning the entire table with every insert with your nested select. It's also an expensive select because you're not using an index. (At least that's what I figure from the partial information. \$\endgroup\$
    – Mataniko
    Commented Dec 3, 2012 at 6:35

3 Answers 3


From the looks of this, you should be using a Stored Procedure, not entirely familiar with how that works in MySQL but I imagine it is similar to SQL Server Stored Procedures.

Using a Stored Procedure would be the easiest way to do this.

You create the stored procedure with the parameters that you need, then in the C# you call the stored procedure giving it the parameters in the specified manner. I haven't tried to do with with MySQL but I imagine it is much the same as with SQL Server ( I know I said that once already )

You are trying to do the Database's work with C#, keep them separate.

Cleanse the input, create a command for the Stored Procedure, give it the parameters, execute the command.

This way you can loop through a list of names in C# sending the names to the stored procedure, or create the stored procedure to accept a list of names.

Creating the Stored Procedure will also allow you to use it other places in the C# application, and if you need to change the Query, it is easily maintainable, you change the query and don't have to recompile the application as long as the input stays the same.


You don't show it here, but I guarantee you're splitting on whitespace. Which is wrong. Condsider the name Jane MaryAnne Vander Werf. You're going to cut off the second half of this person's last name. This is notoriously hard to do in reality. Names don't just fall into our sense of what "good data" looks like. Please see Falsehoods Programmers Believe About Names.

What you really need to do is make sure your input makes sense. If it doesn't, you're eventually going to have problems. Your input needs to be in the form of fixed width columns so that you can do this manipulation based on the position of the character.


A few thoughts:

  1. Pre-process the file to remove duplicates. Assuming the data is formatted appropriately you could sort the file and then walk through it to pull out the first first/last pairing as it appears the middle initial is not considered.

  2. During pre-processing regularize the output into a file that can be loaded into a working table using LOAD DATA INFILE. Let the database get the data inside as fast as it can.

  3. With the data loaded into a working table create a procedure to split the data into the horrible design apparently necessary without the need to send records in using your programming language of choice.

  4. Truncate or drop the working table when you don't need it anymore.

And, a short anecdote on finding good solutions. Assume someone else has demonstrated a system that is ten times faster than yours. Then, start asking yourself how in the world they were able to do so. This can often push you past your initial impressions -- especially if you have a competitive nature.


Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.