CODEWITHSUNDEEP
mysql
karlosuccess
karlosuccess

Reputation: 885

Is it faster to search by column integer or column string in mysql?

I have a table "transactions" with million records

id        trx   secret_string (varchar(50))        secret_id (int(2.))
1         80    52987624f7cb03c61d403b7c68502fb0   1
2         28    52987624f7cb03c61d403b7c68502fb0   1
3         55    8502fb052987624f61d403b7c67cb03c   2
4         61    52987624f7cb03c61d403b7c68502fb0   1
5         39    8502fb052987624f61d403b7c67cb03c   2
..
999997    27    8502fb052987624f61d403b7c67cb03c   2
999998    94    8502fb052987624f61d403b7c67cb03c   2
999999    40    52987624f7cb03c61d403b7c68502fb0   1
1000000   35    8502fb052987624f61d403b7c67cb03c   2

As you can notice, secret_string and secret_id will always match.

Let's say, I need to select records where secret_string = "52987624f7cb03c61d403b7c68502fb0".

Is it faster to do:

SELECT id FROM transactions WHERE secret_id = 1

Than:

SELECT id FROM transactions WHERE secret_string = "52987624f7cb03c61d403b7c68502fb0"

Or it does not matter? What about for other operations such as SUM(trx), COUNT(trx), AVG(trx), etc?

Column secret_id currently does not exist, but if it is faster to search records by it, I am planning to create it upon row insertions.

Thank you

I hope I make sense.

Upvotes: 9

Views: 4225

Answers (2)

Sebastian
Sebastian

Reputation: 506

As the others told you: selecting int is definitly faster than strings. However if you need to select by secret_string, all given strings look like a hex string, that said you can consider to cast those strings to an int (or big int) using hex('52987624f7cb03c61d403b7c68502fb0') and store those int values instead of strings

Upvotes: -1

Leo
Leo

Reputation: 7420

Int comparisons are faster than varchar comparisons, for the simple fact that ints take up much less space than varchars.

This holds true both for unindexed and indexed access. The fastest way to go is an indexed int column.

There is another reason to use an int, and that is to normalise the database. Instead of having the text '52987624f7cb03c61d403b7c68502fb0' stored thousands of times in the table,you should store it's id and have the secret string stored once in a separate table. It's the same deal for other operations such as SUM COUNT AVG.

Upvotes: 5

Related Questions