Designing table index

In a table with an id, several key values and several other "data" columns:

T1 : id , k1 , k2 , k3 , data1, data2, ...

id is unique.

There are several hundreds of distinct values for k1.

For each distinct value of k1 there are several thousands of distinct values for k2.

For each distinct combination of k1,k2 there are a few tens (or a little bit more) rows, where usually k3 be an empty string for all those records and some other k3 values it will only appear once per (k1,k2) and occasionally it will appear two or three times.

I will want to get a set of records where k1=K1V and K2=K2V and K3=K3V, but I also know that this is equivalent to asking about where k1=K1V and K3=K3V or where k2=K2V and K3=K3V because the any specifiv value for K2 will always have the same K1 value.

or maybe I should ask about where K3=K3V and K2=K2V and K1=K1V or where K3=K3V and K1=K1V or where K3=K3V and K2=K2V ? (because of the repentance of the empty string value in the K3 column).

What开发者_C百科 would be the best way to make this query ? and what index should I create for this?

would it change the answer if the variance in the amount of distinct values for K2 on the different values for K1 was known to be big? (i.e. some K1V's have 5 K2V's associated with them while others have thousends)

It sounds like your table is not normalized if there are dependencies between keys. I would suggest normalizing your table to 3rd normal form first, and then putting indexes on the foreign keys (at a minimum). Additional indexes should then be added to support specific queries.