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.