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

Question

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.

Definitely by int. For sure, comparing with 2-4 bytes is faster than comparing with 50-100 bytes — Mojtaba
– Mojtaba, Commented Apr 8, 2017 at 20:11
It should matter, INT should faster than varchar(50) at searching, though you can create test table with million records and with both type of columns and test it. (Hope don't forgot about indexes) — Oto Shavadze
– Oto Shavadze, Commented Apr 8, 2017 at 20:14

Leo · Accepted Answer · 2017-04-08 20:33:47Z

5

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.

answered Apr 8, 2017 at 20:33

Leo

7,4105 gold badges28 silver badges48 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

karlosuccess Over a year ago

Thank you very much for the insight. It is very much of help. Thank you for your time! Cheers!

Sebastian · Accepted Answer · 2017-04-09 09:09:47Z

-1

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

edited Apr 9, 2017 at 9:09

answered Apr 8, 2017 at 20:38

Sebastian

5061 gold badge7 silver badges20 bronze badges

Collectives™ on Stack Overflow

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

2 Answers 2

1 Comment

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Related