Mysql : combining multiple integer fields into one string field?

Question

Let's say I have a table with millions of rows in which I have 3 integral variables: x,y and z against which I do my searching in a SELECT... WHERE x=a and y=b and z=c

Which would be faster / more efficient?

• combining the 3 fields into a separate string column "x_y_z" (e.g. 1231_3242_6864) and indexing it

• Making a 3 column index against the three integers?

Answer 1

No it would be worst, string comparaison are much slower. You could eventually (if really needed, I wouldn't recommend it) combine the 3 integers in one integer but only IF THEY FIT.

However, to solve your index, problem the easiest would be to create a composite index on x,y and z.