Search code examples
mysqlsqldatabaseperformanceindexing

Is multiple field index in MySQL a good choice?

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 4 field (K_Field1,K_Field2,K_Field3,K_Field4) together identify a row uniquely. I created one table, using these fields as its fields.

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

To do that, I need to put a multiple field index on the first 4 columns, which is - I'm afraid - not the best solution. Is there a better database structure to store and search in that data, or I have to live with the four-fielded index?

I'm using MySQL

Solution

  • There's nothing wrong with creating an index on the first 4 columns, in fact you should:

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

    because that is the reality of your situation.

    It is also the "correct" solution.