Mysql Bigint VS Varchar

Question

What is the best for index field, and space token on hard disk/RAM? Biginteger or Varchar(15) ? I can have for example such index number:

from  10000001 to 45281229703 and higher...

But what is better to choose? Also on non-indexing field what field type is better?

Answer 1

BIGINT is always 8 bytes, VARCHAR(15) is 1..16 bytes depending on value length, so BIGINT needs less memory on large numbers, but more memory on small numbers (shorter than 7 digits). Also, BIGINT is faster.

Answer 2

We have run test in simulation environment.

1. Created able with 1 BIGINT and 1VARCHAR parameter.
2. Inserted 30Lac rows.
3. created index on both fields.
4. Result is : BIGINT respond almost 20time's faster than VARCHAR.

Here is script to perform above steps:

Create table r5(mob bigint,m_mob varchar(30));

Create index i_d on r5(mob,m_mob);


do $$
begin
for i in 1..3000000 loop
insert into r5(mob,m_mob) values(i,i||’abc’);
end loop;
end; $$

select * from r5
where mob=2900000;

select * from r5
where m_mob=’2900000abc’;