Mysql IN(VARCHAR) vs IN(INT)

Question

I need to choose between VARCHAR and INT in a many to many relationship

Is it faster to do IN('1','21','311') or IN(1,21,311) in Mysql?

check this out: http://stackoverflow.com/questions/2346920/sql-select-speed-int-vs-varchar — Black Sheep, Sep 26 '13 at 14:20
Don't use `IN()` by identifying a relation at all. Use `EXISTS()`. In any case, use foreign key id's (integers). — Daniel W., Sep 26 '13 at 14:53

score 1 · Accepted Answer · answered Sep 26 '13 at 14:42

1

You'll want to use integers whenever possible since their representation is fixed length, four bytes by default, and comparisons are done with a single operation. Indexing and comparing strings is always more expensive, but the longer the strings get, the bigger the difference.

In your example here, which uses trivially small numbers, they might perform about the same. With real-world numbers that have six or seven digits, though, you'll see a much bigger difference.

answered Sep 26 '13 at 14:42

tadman

208,517
23
234
262

Thanks, and what about the difference between **IN** and **EXISTS** previously mentioned by DanFromGermany in the comments? – RafaSashi Sep 26 '13 at 16:58
1

I've only seen `EXISTS` used with [subqueries](http://dev.mysql.com/doc/refman/5.5/en/exists-and-not-exists-subqueries.html), not fixed lists. – tadman Sep 26 '13 at 17:31

Mysql IN(VARCHAR) vs IN(INT)

1 Answers1