Home > Blockchain >  In MySQL, is it faster to compare with integer or string of integer?
In MySQL, is it faster to compare with integer or string of integer?

Time:10-14

I have a very large table looks like this

int_id       str_id
  1             1
  1             1
  2             2
  3             3
 ...           ...
99999         99999
  3             3

Column int_id has type INT while column str_id has type VARCHAR. They always contain the "same" value (i.e. if int_id is 1, then str_id is "1", etc...).

Now let's say I want to query rows whose id is in (1,4,5,2,....5282,55,232) (a list of around 15 ids), which of the 2 queries below is faster?

select * from table where int_id IN (1,4,5,2,....5282,55,232)

or

select * from table where str_id IN ('1','4','5','2',....'5282','55','232')

assuming I create an index for each column. My table will be absolutely enormous and speed is very important to me so I want to optimize it as much as possible. Appreciate any help.

CodePudding user response:

MySQL ultimately runs on some processor, and in general an integer comparison can be done in a single CPU cycle, while string comparisons will generally take multiple cycles, perhaps one cycle per character. See Why is integer comparison faster then string comparison? for more information.

  • Related