Is MySQL's multiple field index a good choice?

Question

I have a huge data set. The structure looks something like this:

K_Field1, K_Field2, K_Field3, K_Field4, D_Field5, D_Field6, D_Field7, D_field8 

The problem is that only the first field ( K_Field1,K_Field2,K_Field3,K_Field4 ) together identify the line uniquely. I created one table using these fields as fields.

Let's say I have 1 million rows in a table using this structure. If I import a new record, I must decide whether it is already in the database. If so, then I have to update it, if not, then I need to insert a new line.

To do this, I need to put the index of several fields in the first 4 columns, which, I'm afraid, is not the best solution. Is there a better database structure for storing and searching this data, or do I need to live with a four-field index?

I am using MySQL

Answer 1

There is nothing wrong with creating an index in the first 4 columns, in fact you need:

 create unique index mytable_key on mytable(K_Field1,K_Field2,K_Field3,K_Field4); 

because it is the reality of your situation.

This is also the “right” decision.